Sorts the result set by one or more columns.
ORDER BY <expr> [ASC | DESC] [NULLS FIRST | NULLS LAST], ...## Overview ORDER BY sorts the final result set of a query by one or more expressions. Without ORDER BY, the order of rows in a result set is non-deterministic and may vary between executions, even for the same data. ORDER BY is the only clause that guarantees a specific row order in the output. ## Execution Order ORDER BY is evaluated after SELECT and DISTINCT, making it one of the last logical operations in query processing. Because it runs after SELECT, column aliases defined in the SELECT list are visible in the ORDER BY clause. Only LIMIT and OFFSET are evaluated after ORDER BY. ## Behavior - **ASC and DESC**: ASC (ascending) is the default sort direction. DESC reverses the order. Each sort expression can have its own direction. - **NULLS FIRST and NULLS LAST**: Controls where NULL values appear in the sorted output. The default depends on the sort direction: ASC defaults to NULLS LAST (NULLs at the end), and DESC defaults to NULLS FIRST (NULLs at the beginning). Explicit NULLS FIRST or NULLS LAST overrides these defaults. - **Multiple sort keys**: When multiple expressions are listed, rows are first sorted by the first expression; ties are broken by the second expression, and so on. - **Ordinal references**: Integer literals refer to the position in the SELECT list (1-based). `ORDER BY 1` sorts by the first selected column. - **Expression sorting**: ORDER BY accepts arbitrary expressions, including function calls, CASE expressions, and arithmetic. The expression is evaluated for each row and the result is used as the sort key. - **Alias references**: Column aliases from the SELECT list can be used in ORDER BY because ORDER BY is evaluated after SELECT. - **Collation**: String sorting follows the default binary collation. This means uppercase letters sort before lowercase letters in ASCII order. - **Top-N optimization**: When ORDER BY is combined with LIMIT, the optimizer uses a heap-based top-N sort rather than a full sort. This reduces memory usage from O(N) to O(K) where K is the LIMIT value, and avoids sorting the entire result set. - **Interaction with UNION/INTERSECT/EXCEPT**: ORDER BY at the end of a set operation applies to the combined result. To sort individual branches, wrap each SELECT in a subquery. ## Compatibility ORDER BY conforms to the SQL:1992 standard. NULLS FIRST and NULLS LAST conform to SQL:2003. Ordinal position references and alias references in ORDER BY are widely supported extensions.
-- Sort by a single column descending, NULLs at the end
SELECT * FROM products
ORDER BY price DESC NULLS LAST;-- Multi-column sort with mixed directions
SELECT * FROM employees
ORDER BY department ASC, salary DESC;-- Sort by an expression
SELECT product_name, unit_price * quantity AS line_total
FROM order_items
ORDER BY unit_price * quantity DESC;-- Sort by column alias
SELECT region, SUM(amount) AS total_revenue
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;-- Sort by ordinal position
SELECT name, hire_date, salary
FROM employees
ORDER BY 3 DESC, 2 ASC;-- Sort with CASE expression for custom ordering
SELECT name, status
FROM tickets
ORDER BY
CASE status
WHEN 'critical' THEN 1
WHEN 'high' THEN 2
WHEN 'medium' THEN 3
WHEN 'low' THEN 4
ELSE 5
END ASC;