Sorting Rows Retrieved by a Query in SQL
The ORDER BY Clause
The ORDER BY clause is used to specify the order in which rows should be returned by a query. You can sort the results by one or more columns and choose the sort direction as ascending (ASC) or descending (DESC).
Basic Syntax:
SELECT column1, column2, ... FROM table ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Simple Sorting
To sort results based on a single column, use the ORDER BY clause followed by the column name and the sort direction.
Example:
SELECT name, age FROM employees ORDER BY age ASC;
This query retrieves employee names and ages and sorts them by age in ascending order. If ASC is omitted, the sort is ascending by default.
Descending Order Sorting
To sort results in descending order, use DESC after the column name.
Example:
SELECT name, salary FROM employees ORDER BY salary DESC;
This query retrieves employee names and salaries, sorting them by salary in descending order.
Sorting by Multiple Columns
You can sort by multiple columns by specifying each column in the ORDER BY clause. Sorting is done first by the first column, then by the second column if there are ties in the first column, and so on.
Example:
SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;
This query sorts employees first by department in ascending order. If there are ties within departments, it then sorts by salary in descending order.
Sorting with Expressions and Functions
You can also sort results using expressions or functions. This is useful for sorting based on calculated or derived values.
Example:
SELECT name, salary, EXTRACT(YEAR FROM AGE(hire_date)) AS years_with_company FROM employees ORDER BY years_with_company DESC;
This query sorts employees by their years with the company (calculated from their hire date) in descending order.
Sorting with NULL Values
NULL values can be sorted in special ways, typically treated as either the highest or lowest values depending on the database system. SQL standards allow control over this with NULLS FIRST or NULLS LAST.
Example for PostgreSQL:
SELECT name, salary FROM employees ORDER BY salary ASC NULLS LAST;
This query sorts salaries in ascending order, placing NULL values at the end of the result set.
Sorting and Performance
Sorting can impact performance, especially with large datasets. Indexes can help speed up sorting operations. Ensure that columns used for sorting are indexed if performance is a concern.
Performance Tip: When sorting large tables frequently, consider creating indexes on the columns used for sorting to improve performance.
Advanced Examples
Example 1: Conditional Sorting
Suppose you want to prioritize employees with salaries above a certain threshold.
SELECT name, salary FROM employees ORDER BY CASE WHEN salary > 50000 THEN 1 ELSE 2 END, salary DESC;
This query first sorts employees with salaries above 50,000 to the top, and then sorts the remaining employees by salary in descending order.
Example 2: Sorting with Calculated Columns
You can sort by calculated columns, which is useful for sorting based on derived data.
SELECT name, price, price * (1 - discount) AS final_price FROM products ORDER BY final_price DESC;
This query sorts products by their final price (after discount) in descending order.
Conclusion
Sorting rows in SQL is a fundamental feature that allows you to organize query results in a logical and useful manner. Whether you need simple, multi-column sorting or sorting based on calculations, the ORDER BY clause is highly flexible and powerful.