SQL ORDER BY: Sort Query Results (ASC, DESC, Multiple Columns)

A table has no built-in order. Unless you ask for one, the database may return rows in whatever order is fastest for it, and that order can change. ORDER BY sorts the result exactly the way you want.

The example table#

Syntax#

SELECT columns
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

ORDER BY comes after WHERE (and after GROUP BY / HAVING if you use them), and before LIMIT.

Ascending order (the default)#

SELECT name, salary
FROM employees
ORDER BY salary;

name  | salary
------+-------
John  | 46000
Asha  | 52000
Priya | 61000
Ravi  | 78000
Meera | 81000

ASC (ascending: smallest first) is the default, so ORDER BY salary and ORDER BY salary ASC are the same. Text sorts alphabetically, and dates sort from earliest to latest.

Descending order#

Add DESC to sort from largest to smallest:

SELECT name, salary
FROM employees
ORDER BY salary DESC;

name  | salary
------+-------
Meera | 81000
Ravi  | 78000
Priya | 61000
Asha  | 52000
John  | 46000

Sorting by several columns#

List several columns separated by commas. The database sorts by the first column, and uses the next column only to break ties:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

name  | department  | salary
------+-------------+-------
Meera | Engineering | 81000
Ravi  | Engineering | 78000
John  | Marketing   | 46000
Priya | Sales       | 61000
Asha  | Sales       | 52000

Departments are A to Z. Within each department, the highest salary comes first. Each column gets its own ASC or DESC.

Sorting by a calculation or alias#

You can sort by an expression, or by the alias you gave it in SELECT:

SELECT name, salary * 12 AS yearly
FROM employees
ORDER BY yearly DESC;

name  | yearly
------+-------
Meera | 972000
Ravi  | 936000
Priya | 732000
Asha  | 624000
John  | 552000

This works because ORDER BY runs after SELECT, so the alias already exists. (That is not true for WHERE, as you will see in Aliases.)

You can also sort by a column that you do not select:

SELECT name
FROM employees
ORDER BY hire_date;

How NULLs are sorted#

SELECT name, manager_id
FROM employees
ORDER BY manager_id;

name  | manager_id
------+-----------
Meera | NULL
Priya | NULL
Ravi  | 3
Asha  | 5
John  | 5

Where NULLs go depends on the database:

PostgreSQL, Oracle and SQLite let you choose explicitly with NULLS FIRST or NULLS LAST:

SELECT name, manager_id
FROM employees
ORDER BY manager_id NULLS LAST;

Common mistakes#

Practice#

SELECT name, city
FROM employees
ORDER BY city, name;

name  | city
------+----------
Ravi  | Bengaluru
Priya | Delhi
Asha  | Mumbai
John  | Mumbai
Meera | Pune

Key takeaway#

Next, return only the first few rows with LIMIT.