Querying
ORDER BY and Sorting
The ORDER BY clause sorts the result rows of a SELECT statement according to one or more expressions.
Overview
The ORDER BY clause sorts the result rows of a SELECT statement according to one or more expressions. It is part of the SQL standard since SQL-86 and is widely supported. Sorting is typically the final step in query processing, after filtering, grouping, and aggregation. Without ORDER BY, the order of rows returned by a query is implementation-dependent and not guaranteed. ORDER BY can sort by column names, aliases, expressions, or column positions. It supports ascending (ASC) and descending (DESC) order, with ASC as the default. Multiple sort keys can be specified, each with its own direction. The clause may also include NULLS FIRST or NULLS LAST to control where null values appear, though this is not universal. ORDER BY is essential for deterministic output, pagination, and report generation. It is often used with LIMIT and OFFSET to retrieve a stable subset of rows. While sorting can be expensive on large datasets, proper indexing can improve performance. Understanding ORDER BY is fundamental for querying and data presentation.
Syntax and usage
The basic syntax of ORDER BY is: SELECT ... FROM ... WHERE ... ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST], .... The ORDER BY clause appears after the WHERE, GROUP BY, and HAVING clauses, and before LIMIT or OFFSET. Each expression can be a column name, an alias defined in the SELECT list, a computed expression, or an integer referencing the position of a column in the SELECT list. For example, ORDER BY 1 DESC sorts by the first selected column in descending order. The direction defaults to ASC. The NULLS FIRST/LAST option is supported by PostgreSQL, Oracle, and SQLite, but not by MySQL or SQL Server, which treat NULL as lowest value; you can emulate with a CASE expression. Multiple sort keys are separated by commas, and each key can have its own direction. ORDER BY can also be used with set operations like UNION, but only at the end of the entire query. In subqueries, ORDER BY is allowed only with LIMIT/OFFSET or in a context where the order is preserved. Vendor-specific extensions exist, such as COLLATE for case-insensitive sorting.
How it works
Conceptually, ORDER BY sorts the result set after all other operations in the query have been applied. The database engine evaluates the SELECT list, applies WHERE, GROUP BY, HAVING, and window functions, and then sorts the resulting rows. The sort is typically implemented using an in-memory sort algorithm (e.g., quicksort) or an external merge sort if the data exceeds memory. The sort key expressions are evaluated for each row, and rows are compared according to the specified collation and direction. Null values are treated as either smallest or largest depending on the implementation and the NULLS FIRST/LAST option. The sort may be stable or unstable; the SQL standard does not require stability. When multiple keys are provided, the engine compares the first key, then the second, and so on. The output order is deterministic only if the sort keys uniquely identify each row or if the engine's sort is stable. Performance depends on the number of rows, the width of the rows, and the availability of indexes. An index on the sort columns can allow the engine to avoid a sort operation by scanning the index in order.
Examples
Sort employees by last name ascending: SELECT first_name, last_name FROM employees ORDER BY last_name ASC;. Sort by salary descending, then by hire date ascending: SELECT * FROM employees ORDER BY salary DESC, hire_date ASC;. Use column position: SELECT first_name, last_name, salary FROM employees ORDER BY 3 DESC;. Sort with a computed expression: SELECT product_name, price * quantity AS total FROM order_items ORDER BY total DESC;. Control null placement (PostgreSQL): SELECT * FROM customers ORDER BY last_purchase_date DESC NULLS LAST;. Emulate NULLS LAST in MySQL: SELECT * FROM customers ORDER BY last_purchase_date IS NULL, last_purchase_date DESC;. Use ORDER BY with LIMIT: SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;. Sort by multiple columns with mixed directions: SELECT * FROM tasks ORDER BY priority ASC, due_date DESC;. Use ORDER BY with an alias: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users ORDER BY full_name;. Note that ORDER BY can reference aliases in most databases, but not in all contexts.
Common pitfalls
A frequent mistake is assuming that a query without ORDER BY returns rows in a particular order. The SQL standard does not guarantee any order unless ORDER BY is specified. Relying on implicit ordering can lead to inconsistent results, especially after data modifications or when the query plan changes. Another pitfall is using ORDER BY with column positions that are out of range or ambiguous, which can cause errors or unexpected sorting. When sorting by an alias, some databases allow it only in the outermost query, not in subqueries or expressions. Mixing ASC and DESC incorrectly can produce unintended order. Null handling varies: in MySQL and SQL Server, NULLs sort first in ascending order and last in descending order, while PostgreSQL and Oracle default to NULLs last in ascending and first in descending. This can lead to portability issues. Sorting large result sets without an index can be slow and memory-intensive. Using ORDER BY in a subquery without LIMIT is often meaningless and may be disallowed. Finally, ORDER BY on a column with a non-deterministic collation can yield different orders across systems.
Best practices
Always specify ORDER BY when the order of rows matters, such as for pagination, reports, or user-facing lists. Use explicit column names or aliases rather than column positions, as positions are fragile when the SELECT list changes. For deterministic pagination, include a unique tiebreaker column (e.g., primary key) in the ORDER BY clause. Be aware of null ordering and use NULLS FIRST/LAST if supported, or emulate it with a CASE expression for portability. When sorting by an expression, consider creating an index on that expression to improve performance. Avoid sorting large datasets unnecessarily; apply WHERE and LIMIT to reduce the number of rows. Use ASC or DESC explicitly for clarity, even though ASC is the default. In complex queries with UNION, place ORDER BY at the very end to sort the final result. Test queries on representative data to ensure the desired order, especially when using collations or case-insensitive sorting. Document any assumptions about row order in application code.
Frequently asked questions
Q: Does ORDER BY guarantee a stable sort? A: The SQL standard does not require a stable sort. If two rows have equal sort keys, their relative order is unspecified. To ensure deterministic order, include a unique column in the ORDER BY list. Q: Can I use ORDER BY in a subquery? A: In standard SQL, ORDER BY in a subquery is only allowed if the subquery also has a LIMIT or OFFSET, or in a context where the order is preserved (e.g., with FETCH FIRST). Many databases permit it but may ignore the order. Q: How do I sort NULLs first or last? A: Use the NULLS FIRST or NULLS LAST clause if your database supports it (PostgreSQL, Oracle, SQLite). For MySQL or SQL Server, use an expression like ORDER BY column IS NULL, column to control null placement. Q: Can I sort by multiple columns? A: Yes, separate them with commas, and specify direction for each: ORDER BY col1 ASC, col2 DESC.