from a subquery and applies additional filtering in the outer query.
8.3 ORDER BY in SELECT TOP and LIMIT Clauses
The ORDER BY clause is frequently used in combination with TOP (SQL Server) or LIMIT (MySQL/PostgreSQL) to retrieve the top N rows.
SQL Server example:
SELECT TOP 5 first_name, salary
FROM employees
ORDER BY salary DESC;
MySQL/PostgreSQL example:
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
This is useful for fetching the highest or lowest values from a dataset.
9. Practical Examples and Use Cases
9.1 Simple ORDER BY Example
SELECT * FROM customers
ORDER BY last_name ASC;
9.2 Sorting by Multiple Columns
SELECT * FROM orders
ORDER BY customer_id, order_date DESC;
9.3 Sorting with Expressions and Functions
SELECT product_name, price, price * 0.15 AS tax
FROM products
ORDER BY tax DESC;
9.4 ORDER BY with Aggregate Functions
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY product_count DESC;
9.5 Handling NULL Values in ORDER BY
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date ASC NULLS LAST;
The ORDER BY clause is a vital part of SQL queries that allows you to sort results based on one or more columns. Understanding how it works and how to use it effectively can significantly improve the readability and usefulness of your data. Whether you’re working with simple selections or complex joins and aggregates, ORDER BY provides a powerful mechanism to organize your results. Use it wisely, optimize with indexing, and avoid common performance pitfalls to get the best out of your queries.
Let me know if you’d like this content as a downloadable document or continued with visual diagrams and use cases!