ORDER BY — SQL Tutorial with Examples

What You'll Learn

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.

Syntax

SELECT cols FROM table ORDER BY col ASC;
SELECT cols FROM table ORDER BY col1 DESC, col2 ASC;

Example

-- 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;

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1)
-- 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

Sort by things that aren't in your SELECT

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 hidden

Contrast with WHERE (Topic 3): it runs BEFORE SELECT and cannot see aliases. Pipeline position, not syntax, decides visibility.

Ties are nondeterministic — always add a tiebreaker

When 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;

Any result you paginate, export, or diff should append a unique column (usually the PK) as its final sort key.

NULL placement and collation: sorting is database-specific

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;

A 'stable, portable order' = explicit NULL placement + explicit tie direction + a unique tiebreaker.

Common Mistakes

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.

Interview Tips

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).

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

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?

Practice

Return every employee's name and salary, highest salary first.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why is ORDER BY inside a view sometimes ignored?

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.

Does ORDER BY cost anything?

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.