Loading…
Loading…
SQL tutorial · Beginner
⏱️ 5 mins read
ORDER BY sorts the result set. Default is ASC (ascending, A→Z, 0→9). Use DESC for descending. You can sort by multiple columns — the second sort applies within ties of the first. Without ORDER BY, SQL makes NO guarantee about row order.
SELECT cols FROM table ORDER BY col ASC;
SELECT cols FROM table ORDER BY col1 DESC, col2 ASC;-- Highest salary first
SELECT name, salary FROM employees
ORDER BY salary DESC;
-- Multi-column: by department, then salary high-low within each
SELECT name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;| id | name | department_id | salary | hire_date |
|---|---|---|---|---|
| 1 | Ada | 1 | 145000 | 2021-03-01 |
| 2 | Grace | 1 | 115000 | 2022-06-13 |
| 3 | Alan | 2 | 92000 | 2020-11-02 |
| 4 | Edsger | NULL | 78000 | 2023-08-19 |
| 5 | Barbara | 3 | 64000 | 2024-01-08 |
ORDER BY runs near the end of the logical pipeline — after SELECT — so it can reference output aliases and even columns you never returned. That lets a report sort by internals while exposing only what the reader should see.
-- Sort by an alias computed in SELECT:
SELECT name, salary * 12 AS annual_cost
FROM employees
ORDER BY annual_cost DESC;
-- Sort by a column you never return:
SELECT name FROM employees
ORDER BY hire_date; -- seniority order, hire_date stays hiddenWhen two rows share the same sort key, the engine may return them in any order — and that order can change between runs, after an ANALYZE, or across pages. Combined with LIMIT (Topic 5), unstable ties mean duplicate or missing rows between page views.
-- Grace and Ada could come back in either order... or swap between pages
SELECT name, department_id FROM employees
ORDER BY department_id;
-- A unique tiebreaker makes the order total and stable:
SELECT name, department_id FROM employees
ORDER BY department_id ASC, id ASC;NULLs sort LAST in ascending order in PostgreSQL and Oracle, but FIRST in MySQL and SQL Server. Text order depends on collation: with German collation, 'ä' sorts near 'a'; with binary collation it sorts after 'z'. 'Sorted' is not portable until you specify both.
-- PostgreSQL: explicit NULL placement
SELECT name, department_id FROM employees
ORDER BY department_id ASC NULLS LAST;
-- Portable alternative: order by a NULL flag first (false sorts before true)
SELECT name, department_id FROM employees
ORDER BY (department_id IS NULL), department_id;Assuming result rows have a guaranteed order without ORDER BY. The database engine may return rows in any order. Never rely on implicit ordering in production queries.
Sorting by column position (ORDER BY 2) is valid SQL but avoid it in production — column order can change. Interviewers like to ask about NULL ordering: NULLs sort LAST in ASC, FIRST in DESC by default (varies by DB).
1. Can ORDER BY reference an alias computed in the SELECT list?
2. Can ORDER BY sort by a column you never returned in SELECT?
3. Two employees share the same sort key. What does the engine return?
4. What does a query WITHOUT ORDER BY guarantee about row order?
5. In ascending order, where do NULLs sort in PostgreSQL?
6. Which ORDER BY makes results fully deterministic?
Return every employee's name and salary, highest salary first.
⚡ Solve it in the SQL playground →The standard says views are unordered sets; an outer query may legally reorder or parallelize results. Relying on ORDER BY inside a view, subquery, or CTE is a bug in the making — put the ORDER BY on the outermost query.
Sorting is one of the classic blockers: for large unsorted inputs the engine must materialize and sort rows, which can spill to disk. An index whose column order matches your ORDER BY can be read pre-sorted — Topic 21 (Indexes) revisits this.