SQL Reference ยท Statements and Clauses
SQL ORDER BY clause
Reference for the SQL ORDER BY clause: sort ascending or descending, sort by several columns, expressions and positions, NULL ordering and dialect rules.
ORDER BY sorts the rows of a result. Without it, a database may return rows in any order, and that order can change between runs. If a page, report or export must be stable, always sort explicitly.
You can sort by one or more columns, by expressions, by a select-list alias, or by the position of a column in the select list. Each sort key can be ascending (ASC, the default) or descending (DESC). Later keys break ties in earlier ones.
SELECT name, price FROM products ORDER BY price DESC, name;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
ORDER BY sort_key [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, sort_key ...]
| Part | Description |
|---|---|
sort_key |
A column, an expression, a select-list alias or a 1-based position (ORDER BY 2). |
ASC / DESC |
Ascending (smallest first, the default) or descending. Applies to the key right before it only. |
NULLS FIRST / NULLS LAST |
Where NULL sorts. Supported by PostgreSQL, Oracle and SQLite 3.30+; not by MySQL or SQL Server. |
Returns: the same rows, in sorted order. ORDER BY is evaluated after the select list, so aliases are visible to it.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| NULL position, ascending | First | Last | First | First | Last |
NULLS FIRST/LAST |
Yes (3.30+) | Yes | No | No | Yes |
| Text comparison | Binary by default | Collation | Collation, case-insensitive default | Collation, case-insensitive default | Binary by default |
-- MySQL and SQL Server: put NULLs last when sorting ascending SELECT name, manager_id FROM employees ORDER BY manager_id IS NULL, manager_id;
Usage notes
- Sort is not stable by default. Equal keys can come back in any order. Add a unique column (such as
id) as the last key for repeatable pagination. DESCapplies per key.ORDER BY a, b DESCsortsaascending andbdescending.- Positions are brittle.
ORDER BY 2breaks silently when someone edits the select list; prefer names. - Ordering by an alias works in all dialects, but you cannot use an alias inside an expression in
ORDER BYin SQL Server and PostgreSQL. - Mixed types in SQLite sort by storage class: NULL, numbers, text, blobs.
- Cost: sorting a large result needs memory or a temp file. An index matching the sort keys can avoid the sort.
- Subqueries and views: an
ORDER BYinside them has no guaranteed effect on the outer query unless paired withLIMIT/TOP.
Related
LIMITandOFFSET,SELECT,GROUP BY,CASE- Lessons: ORDER BY, LIMIT and OFFSET