Back to all posts

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 BY comes after WHERE, GROUP BY and HAVING. Only LIMIT, OFFSET and FETCH FIRST come after it.
  • column1 is 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).
  • ASC sorts from the smallest value up.
  • DESC sorts from the largest value down.
  • column2 sorts 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:

The five employees as stored, and the same rows after ORDER BY department, salary DESC: Sara in HR, then Tom before John in Marketing, then Maria before Alex in Sales

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;
namesalary
John3000
Sara4000
Alex5000
Tom5500
Maria6000

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;
namesalary
Maria6000
Tom5500
Alex5000
Sara4000
John3000

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;
namedepartmentsalary
SaraHR4000
TomMarketing5500
JohnMarketing3000
MariaSales6000
AlexSales5000

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;
nameannual_salary
Maria72000
Tom66000
Alex60000
Sara48000
John36000

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;
namedepartmentsalary
MariaSales6000
TomMarketing5500
AlexSales5000
SaraHR4000
JohnMarketing3000

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;
departmenttotal_salary
Sales11000
Marketing8500
HR4000

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;
namedepartment
AlexSales
MariaSales
JohnMarketing
TomMarketing
SaraHR

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;
namesalary
Maria6000
Tom5500

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;
namesalary
Maria6000
Tom5500
Alex5000
Sara4000
John3000
NinaNULL

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:

enginenamedepartment
PostgreSQL 18.6SaraHR
MySQL 9.1NinaHR
SQLite 3.50SaraHR

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:

engineNULL in ASCNULL in DESC
PostgreSQLlastfirst
Oraclelastfirst
MySQLfirstlast
SQLitefirstlast
SQL Serverfirstlast

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.

The ORDER BY department, salary DESC query in the DbSchema SQL Editor, with Sara, Tom, John, Maria and Alex in the result grid under the editor

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.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download