SQL ORDER BY: Syntax, ASC/DESC, Multiple Columns, and Pagination
For SQL beginners who write SELECT queries and need the rows back in a set order.
On this page
ORDER BY sorts the result of a SELECT by one or more columns. For example, you can list the newest orders first. Without ORDER BY, the database returns the rows in whatever order it reads them, and that order can change from one run to the next.
ORDER BY has this shape:
SELECT columns
FROM table_name
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC]
ORDER BYcomes after WHERE, GROUP BY and HAVING. Only LIMIT, OFFSET and FETCH FIRST come after it.column1is what the rows are sorted by: a column, a calculation, a column alias, or the column's position in the select list (the columns after SELECT).ASCsorts from the smallest value up.DESCsorts from the largest value down.column2sorts the rows that have the same value in column1. Rows with the same value are called a tie. Each column takes its own ASC or DESC.
Create a sample table of five employees and fill it:
CREATE TABLE employees (
employee_id int PRIMARY KEY,
name varchar(20),
salary int,
department varchar(20)
);
INSERT INTO employees VALUES
(1, 'John', 3000, 'Marketing'),
(2, 'Alex', 5000, 'Sales'),
(3, 'Sara', 4000, 'HR'),
(4, 'Maria', 6000, 'Sales'),
(5, 'Tom', 5500, 'Marketing');
Every statement below was run on PostgreSQL 18.6, MySQL 9.1 and SQLite 3.50. All three database engines returned the same rows unless an engine is named. SQL Server and Oracle were not run, so their behavior comes from their documentation.
Here are the five rows before and after ORDER BY department, salary DESC:
ASC
ASC sorts from the smallest value to the largest. For example, you can list the employees from the lowest salary to the highest.
SELECT name, salary
FROM employees
ORDER BY salary;
| name | salary |
|---|---|
| John | 3000 |
| Sara | 4000 |
| Alex | 5000 |
| Tom | 5500 |
| Maria | 6000 |
John earns the least, so he comes first, and Maria's 6000 comes last. ASC is the default, so ORDER BY salary ASC returns the same rows.
On a date column, ASC puts the oldest date first. On a text column such as name, ASC sorts from A to Z.
DESC
DESC sorts from the largest value to the smallest. For example, you can list the employees from the highest salary to the lowest.
SELECT name, salary
FROM employees
ORDER BY salary DESC;
| name | salary |
|---|---|
| Maria | 6000 |
| Tom | 5500 |
| Alex | 5000 |
| Sara | 4000 |
| John | 3000 |
Maria's 6000 now comes first and John's 3000 last. In ORDER BY department, salary DESC, DESC applies to salary only.
Multiple columns
A second column after a comma sorts the rows that tie on the first column. For example, you can sort the employees by department, and by salary inside each department.
SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC;
| name | department | salary |
|---|---|---|
| Sara | HR | 4000 |
| Tom | Marketing | 5500 |
| John | Marketing | 3000 |
| Maria | Sales | 6000 |
| Alex | Sales | 5000 |
The departments run from HR to Sales, in ascending order. Inside Marketing and Sales, salary DESC puts the higher salary first: Tom before John, Maria before Alex. HR has one employee, so salary has nothing to sort there.
Column aliases
ORDER BY can sort by a column alias: the name that AS gives a column or a calculation in the select list. For example, you can calculate the yearly salary once and sort by its alias.
SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
| name | annual_salary |
|---|---|
| Maria | 72000 |
| Tom | 66000 |
| Alex | 60000 |
| Sara | 48000 |
| John | 36000 |
annual_salary is each salary times 12, and the rows run from Maria's 72000 down to John's 36000. ORDER BY can use the alias, because the database calculates the select list before it sorts.
In PostgreSQL, ORDER BY accepts the alias only on its own, not inside a calculation. ORDER BY annual_salary + 1 fails with column "annual_salary" does not exist. MySQL and SQLite accept annual_salary + 1.
SQL Server's documentation also asks for the alias on its own. SQL aliases explains aliases in the rest of a query.
Column positions
A number in ORDER BY stands for a column of the select list, counted from 1. For example, in a quick query, ORDER BY 3 saves typing the third column's name.
SELECT name, department, salary
FROM employees
ORDER BY 3 DESC;
| name | department | salary |
|---|---|---|
| Maria | Sales | 6000 |
| Tom | Marketing | 5500 |
| Alex | Sales | 5000 |
| Sara | HR | 4000 |
| John | Marketing | 3000 |
The third column is salary, so the rows come back with the highest salary first. If someone later adds a column before salary in the select list, ORDER BY 3 sorts by that new column without an error. SQL Server's documentation advises against column positions for this reason.
ORDER BY with GROUP BY
ORDER BY runs after GROUP BY, so ORDER BY can sort the groups by a total. For example, you can list the departments from the highest salary total to the lowest.
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;
| department | total_salary |
|---|---|
| Sales | 11000 |
| Marketing | 8500 |
| HR | 4000 |
GROUP BY made one row per department, and ORDER BY sorted those three rows by their total. Sales comes first, with Maria's 6000 and Alex's 5000. A WHERE clause removes rows before GROUP BY and ORDER BY run, so ORDER BY never sees the removed rows.
GROUP BY, SUM, COUNT and AVG and HAVING have tutorials of their own.
CASE in ORDER BY
A CASE expression in ORDER BY gives each value a number, and ORDER BY sorts by that number. For example, you can list the Sales employees first, then Marketing, then everyone else.
SELECT name, department
FROM employees
ORDER BY
CASE department
WHEN 'Sales' THEN 1
WHEN 'Marketing' THEN 2
ELSE 3
END,
name;
| name | department |
|---|---|
| Alex | Sales |
| Maria | Sales |
| John | Marketing |
| Tom | Marketing |
| Sara | HR |
CASE gives Sales 1 and Marketing 2. HR matches no WHEN, so ELSE gives HR 3. The second sort column, name, sorts the employees inside each department from A to Z.
ORDER BY with LIMIT
LIMIT keeps only the first rows of the sorted result, so ORDER BY decides which rows you get. For example, you can list the two highest-paid employees.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;
| name | salary |
|---|---|
| Maria | 6000 |
| Tom | 5500 |
ORDER BY put the two highest salaries first, and LIMIT 2 kept those two rows. Add OFFSET 2 after LIMIT 2 to skip the first two rows. The query then returns the next page of two rows: Alex and Sara.
On SQL Server, write TOP (2) after SELECT. On Oracle and in the SQL standard, write FETCH FIRST 2 ROWS ONLY. TOP, LIMIT, FETCH FIRST and ROWNUM shows each one.
Practice this in Lesson 5 of the SQL tutorial: you run ORDER BY and LIMIT on a shop database in your browser, ties included, and each exercise is checked.
NULLS FIRST and NULLS LAST
NULLS LAST puts NULL, the mark of a missing value, after all other values. NULLS FIRST puts NULL first. For example, you can list the salaries from highest to lowest, with the unknown salaries at the end.
Add a sixth employee whose salary is unknown:
INSERT INTO employees VALUES (6, 'Nina', NULL, 'HR');
SELECT name, salary
FROM employees
ORDER BY salary DESC NULLS LAST;
| name | salary |
|---|---|
| Maria | 6000 |
| Tom | 5500 |
| Alex | 5000 |
| Sara | 4000 |
| John | 3000 |
| Nina | NULL |
The salaries run from 6000 down, and Nina comes last. Without NULLS LAST, PostgreSQL would put Nina first, because PostgreSQL sorts NULL as larger than any value.
PostgreSQL, Oracle, and SQLite from version 3.30 accept NULLS FIRST and NULLS LAST. MySQL 9.1 rejects them with a syntax error near 'NULLS LAST'. SQL Server's syntax has no such option.
In MySQL, put salary IS NULL first in ORDER BY: ORDER BY salary IS NULL, salary DESC returns the same six rows. salary IS NULL is 0 for a salary and 1 for a NULL, so Nina sorts last.
In SQL Server, write the NULL test as a CASE expression: ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary DESC. SQL NULL values explains what else NULL does to a query.
What goes wrong
Rows without ORDER BY
A query without ORDER BY has no defined order. PostgreSQL's documentation says that the order then "must not be relied on". It depends on how the database chooses to run the query, and on the order of the rows on disk.
Put ORDER BY in the outermost SELECT. A subquery is a SELECT inside another query, and a view is a saved query. An ORDER BY inside either one does not fix the order of the outer query's rows.
SQL Server rejects ORDER BY in a subquery unless the subquery also has TOP, or OFFSET with FETCH.
Ties under LIMIT
Rows that tie on every ORDER BY column can come back in either order. Under LIMIT, either of those rows can come first. With Nina in the table, Sara and Nina are both in HR:
SELECT name, department
FROM employees
ORDER BY department
LIMIT 1;
Each engine returned one row:
| engine | name | department |
|---|---|---|
| PostgreSQL 18.6 | Sara | HR |
| MySQL 9.1 | Nina | HR |
| SQLite 3.50 | Sara | HR |
Without LIMIT, the same MySQL query listed Sara before Nina, so adding LIMIT changed the order of the tie. PostgreSQL's documentation calls the rows from such a LIMIT "an unpredictable subset".
Add a column with unique values as the last sort column: ORDER BY department, employee_id. Then all three engines return Sara with LIMIT 1, and Nina with LIMIT 1 OFFSET 1.
NULL at a different end on each engine
If a query has no NULLS FIRST or NULLS LAST, each engine picks its own place for NULL. This query puts Nina at different ends:
SELECT name, salary
FROM employees
ORDER BY salary;
Where Nina lands, by engine:
| engine | NULL in ASC | NULL in DESC |
|---|---|---|
| PostgreSQL | last | first |
| Oracle | last | first |
| MySQL | first | last |
| SQLite | first | last |
| SQL Server | first | last |
The Oracle and SQL Server entries come from their documentation. Oracle 19c sorts NULL after all other values in ascending order, and SQL Server treats NULL as the lowest value.
A report that moves from PostgreSQL to MySQL gets a different first row from the same query. Where the place of NULL matters, write NULLS FIRST or NULLS LAST into the query, or the IS NULL sort shown above.
Run the queries in DbSchema
The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each query and try other ORDER BY columns. Connecting to a database and the SQL Editor are part of the free Community Edition.

