SQL Order By
The ORDER BY clause controls the sequence in which rows come back from a query. Without it, a database is free to return rows in whatever order is most convenient internally, often the order rows happen to be stored in, which can change between runs, after an update, or after a query optimizer picks a different plan. ORDER BY lets you guarantee a predictable, meaningful sequence, such as highest salary first, oldest customer first, or alphabetical by name. It is one of the most frequently used clauses in SQL because raw, unsorted data is rarely useful in a report, an API response, or a UI table.
Overview: How ORDER BY Works
ORDER BY is written at the very end of a SELECT statement (after WHERE, GROUP BY, and HAVING, and before LIMIT). It tells the database engine how to arrange the final result set, not how to process the data internally. You can sort by one column, by several columns in priority order, by a computed expression, by a column alias defined in the SELECT list, or even by a column’s ordinal position in that list.
Under the hood, the engine has a few ways to satisfy an ORDER BY. If an index already exists on the sorting column(s) and the query plan can scan that index in order, the engine can avoid a separate sort step entirely, simply reading rows in index order. If no usable index exists, the engine performs an explicit sort: for small result sets this happens in memory; for large result sets the engine may need to spill intermediate data to a temporary file on disk. This is why sorting a huge, unindexed table can be one of the more expensive operations in a query plan, and why adding an index on frequently-sorted columns is a common performance tuning move.
It is also worth understanding that ORDER BY is one of the last clauses to run logically. By the time sorting happens, filtering (WHERE), grouping (GROUP BY/HAVING), and column selection (SELECT) have already been resolved. That is precisely why ORDER BY is allowed to reference a column alias created in the SELECT list, something most other clauses cannot do.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC] [NULLS FIRST|NULLS LAST],
column2 [ASC|DESC] [NULLS FIRST|NULLS LAST], ...
LIMIT n;| Part | Meaning |
|---|---|
column1, column2, ... |
Column name(s), expression(s), or ordinal position(s) to sort by, evaluated in order (first term is the primary sort key) |
ASC |
Ascending order (smallest/earliest/A first) — this is the default if omitted |
DESC |
Descending order (largest/latest/Z first) |
NULLS FIRST / NULLS LAST |
Explicitly controls where NULL values land; without it, default NULL placement varies by database engine |
Ordinal position (e.g. ORDER BY 2) |
Sort by the Nth column in the SELECT list, counting from 1 |
LIMIT n |
Often paired with ORDER BY to fetch a "top N" result after sorting |
Each sort key can independently have its own ASC or DESC; the keyword only applies to the column immediately before it, not to the whole list. You can also sort by position or by a computed alias, as shown below.
SELECT name, salary FROM employees ORDER BY 2 DESC;
SELECT name, salary, salary * 0.10 AS bonus FROM employees ORDER BY bonus DESC;The first statement sorts by the 2nd selected column (salary) in descending order. The second sorts by bonus, an alias defined in the SELECT list itself, which only works because ORDER BY runs after the SELECT list has been evaluated. Both statements return employees from highest to lowest salary, since bonus is simply salary scaled by a constant.
Examples
The examples below use a small employees table with these sample rows:
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | Alice Johnson | Engineering | 95000 | 2019-03-15 |
| 2 | Bob Smith | Sales | 65000 | 2021-07-01 |
| 3 | Carol White | Engineering | 105000 | 2018-11-20 |
| 4 | David Brown | Marketing | 58000 | 2022-01-10 |
| 5 | Eve Davis | Sales | 72000 | 2020-05-30 |
| 6 | Frank Miller | Engineering | 89000 | 2023-02-14 |
Example 1: Simple ascending sort
SELECT name, salary
FROM employees
ORDER BY salary;| name | salary |
|---|---|
| David Brown | 58000 |
| Bob Smith | 65000 |
| Eve Davis | 72000 |
| Frank Miller | 89000 |
| Alice Johnson | 95000 |
| Carol White | 105000 |
No ASC/DESC was specified, so the engine defaults to ascending, lowest salary first. This is the simplest and most common form of ORDER BY.
Example 2: Descending sort
SELECT name, department, salary
FROM employees
ORDER BY salary DESC;| name | department | salary |
|---|---|---|
| Carol White | Engineering | 105000 |
| Alice Johnson | Engineering | 95000 |
| Frank Miller | Engineering | 89000 |
| Eve Davis | Sales | 72000 |
| Bob Smith | Sales | 65000 |
| David Brown | Marketing | 58000 |
Adding DESC reverses the sort so the highest earner appears first. This pattern, combined with LIMIT, is how most "top N" reports are written.
Example 3: Sorting by multiple columns
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;| name | department | salary |
|---|---|---|
| Carol White | Engineering | 105000 |
| Alice Johnson | Engineering | 95000 |
| Frank Miller | Engineering | 89000 |
| David Brown | Marketing | 58000 |
| Eve Davis | Sales | 72000 |
| Bob Smith | Sales | 65000 |
Rows are grouped alphabetically by department first (the primary key), and within each department group, sorted by salary from highest to lowest (the secondary key). This is the standard way to produce "grouped and ranked" reports without using window functions.
How It Works Step by Step
Conceptually, a SQL query is processed in this logical order, regardless of how it’s typed:
FROM— identify the source table(s) and perform any joinsWHERE— filter individual rowsGROUP BY— collapse rows into groupsHAVING— filter groupsSELECT— compute the output columns and aliasesDISTINCT— remove duplicate rows, if requestedORDER BY— sort the final result setLIMIT/OFFSET— trim the sorted result to a subset
Because ORDER BY runs after LIMIT‘s sibling steps but before LIMIT itself is applied, pairing them gives you deterministic "top N" results: the engine sorts the entire matching set first, then keeps only the first n rows.
SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;| name | department | salary |
|---|---|---|
| Carol White | Engineering | 105000 |
| Alice Johnson | Engineering | 95000 |
| Frank Miller | Engineering | 89000 |
This returns exactly the three highest-paid employees. If you swapped the clause order and tried to LIMIT before sorting, you’d get an arbitrary 3 rows sorted afterward, which is not the same result — this is why SQL requires ORDER BY to come before LIMIT in the statement itself.
Common Mistakes
Mistake 1: Referencing a column that doesn’t exist
SELECT name, salary
FROM employees
ORDER BY emp_salary DESC;This fails with an error like no such column: emp_salary. The actual column is named salary. ORDER BY can only reference columns that exist in the source table or in the SELECT list (as an alias or position) — it cannot invent a name.
SELECT name, salary
FROM employees
ORDER BY salary DESC;The corrected version uses the real column name and runs successfully.
Mistake 2: Assuming ASC/DESC applies to the whole list
SELECT name, department, salary FROM employees ORDER BY department, salary DESC;
SELECT name, department, salary FROM employees ORDER BY department DESC, salary DESC;The first query only marks salary as descending; department silently defaults to ascending, giving Engineering first, then Marketing, then Sales. If the intent was to sort both columns in descending order, you must say so explicitly for each one, as in the second query, which puts Sales first, then Marketing, then Engineering. ASC/DESC is per-column, never inherited from a neighboring column.
Mistake 3: Assuming NULLs sort the same way everywhere
SELECT name, hire_date FROM employees ORDER BY hire_date ASC;
SELECT name, hire_date FROM employees ORDER BY hire_date ASC NULLS LAST;Suppose one employee (Grace Lee) has a NULL hire_date. In SQLite, NULL is treated as the smallest possible value, so the first query places Grace Lee’s row first in ascending order, ahead of every real date. Many developers expect NULLs to sort last instead (which is PostgreSQL’s default behavior for ASC, the opposite of SQLite’s). Rather than relying on engine defaults, use the explicit NULLS FIRST / NULLS LAST modifiers, as in the second query, to get consistent, portable behavior.
Best Practices
- Never rely on a table’s storage or insertion order to imply a meaningful sequence — always add an explicit
ORDER BYwhen order matters. - Prefer explicit column names over ordinal positions (
ORDER BY 2) in production code; positions silently break if theSELECTlist is reordered later. - Pair
ORDER BYwithLIMITfor efficient "top N" queries, and make sure the sort column is indexed on large tables to avoid an expensive full sort. - Be explicit with
NULLS FIRST/NULLS LASTwhenever a column can containNULLs and their position matters, since default behavior differs across database engines. - When paginating with
LIMIT/OFFSET, always include a unique tie-breaking column (like a primary key) as the final sort key so rows don’t shift between pages. - Sort as late as possible in a query pipeline; don’t sort intermediate subqueries unless the outer query specifically needs that order preserved.
Practice Exercises
Using the employees table shown above (id, name, department, salary, hire_date):
- Write a query that lists every employee’s name and hire_date, ordered from most recently hired to earliest hired.
- Write a query that lists name, department, and salary, sorted alphabetically by department, and within each department, alphabetically by name.
- Write a query that returns only the two lowest-paid employees, using
ORDER BYtogether withLIMIT.
Summary
ORDER BYsorts the final result set of a query; without it, row order is not guaranteed.- Default sort direction is ascending (
ASC); useDESCfor descending, and specify it per column. - You can sort by column name, ordinal position, a
SELECT-list alias, or a computed expression. - Multiple sort keys are evaluated left to right: the first column breaks ties for the whole set, later columns only break ties within equal values of earlier ones.
- Logically,
ORDER BYruns afterWHERE,GROUP BY/HAVING, andSELECT, but beforeLIMIT/OFFSET. - Use
NULLS FIRST/NULLS LASTto controlNULLplacement explicitly, since defaults vary between database engines. - Index the sort column(s) on large tables so the engine can avoid an expensive explicit sort.
