QuerydrillORDER BY
ORDER BY sorts the result rows: ORDER BY salary DESC puts the highest salary first, ORDER BY country, name sorts by country and then by name within each country. ASC (the default) is ascending, DESC is descending. It is the last clause before LIMIT.
Syntax
SELECT columns FROM table ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];| Part | What it is |
|---|---|
column or expression | What to sort on; can be a column, an alias from SELECT, or an expression |
ASC / DESC | Direction per column; ASC is the default |
Examples
SELECT name, salary FROM employees ORDER BY salary DESC;Result: Highest salary first
SELECT name, country, city FROM customers ORDER BY country ASC, city DESC;Result: Countries A to Z; within each country, cities Z to A
Each column gets its own direction; DESC does not carry over to the next column.
SELECT name, manager_id FROM employees ORDER BY manager_id NULLS LAST;Result: Employees with a manager first, those with NULL manager_id at the end
NULLS FIRST / NULLS LAST works in PostgreSQL and in SQLite 3.30 and later. By default SQLite puts NULLs first in ASC order and PostgreSQL puts them last.
Common mistakes
- Writing ORDER BY before WHERE or LIMIT is a syntax error; the order is WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
- Expecting ORDER BY a, b DESC to sort both columns descending; only b is descending, so write ORDER BY a DESC, b DESC.
- Sorting numbers stored as text gives 10 before 9; cast the column (ORDER BY CAST(code AS INTEGER)) or fix the column type.
Practice ORDER BY
These drills check your answer on live data, free, no account needed.
- Top three earnersReturn the name and salary of the three highest-paid employees, highest salary first.
- Sort on two columnsReturn every customer's name and country, sorted by country A to Z and, within a country, by name A to Z.
Questions
How do I sort by multiple columns in SQL?
List them in priority order separated by commas: ORDER BY country, city, name. Rows are sorted by the first column, ties by the second, and so on.
Can I ORDER BY a column that is not in SELECT?
Yes in a plain query, for example SELECT name FROM employees ORDER BY salary. With DISTINCT or UNION the sort column must appear in the SELECT list.
Related
- LIMIT and OFFSETLIMIT returns only the first N rows of a query and OFFSET skips rows before that. Get the top 10, page through results and find the second highest value.
- SELECTHow to write a SQL SELECT: choose columns from a table, select everything with *, compute expressions, and combine it with WHERE, ORDER BY and LIMIT.
- DISTINCTSELECT DISTINCT returns each distinct value or combination once. List unique countries, count distinct values, and see why DISTINCT dedupes whole rows.
- ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.