What you will be able to do
After this lesson you can put results in the order you want: highest salary first, newest hire first, alphabetical by name, or by department and then by pay. You will also know why rows come back in a surprising order when you do not ask for one.
Rows have no built-in order
A table is a set of rows, and a set has no first or last element. When you run SELECT * FROM employees, the rows often come back in id order, because that is how the database happens to have stored them. That is a coincidence of storage, not a promise. After an update, an index change or a database upgrade, the same query may return the rows in a different order. If order matters to you, ask for it with ORDER BY, which is the last clause before any row limit.
Sorting by one column
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date
LIMIT 4;+------------+------------+
| first_name | hire_date |
+------------+------------+
| Lata | 2016-04-25 |
| Vikram | 2017-11-02 |
| Meera | 2018-03-12 |
| Dev | 2018-10-15 |
+------------+------------+The default direction is ascending: smallest number, earliest date or alphabetically first text comes first. ASC spells it out. For the other way round, add DESC:
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
| Vikram | 150000 |
| Arjun | 125000 |
| Kavya | 125000 |
| Lata | 115000 |
+------------+--------+(LIMIT is used here to keep the output short. The next lesson covers it.)
Sorting by several columns
Give a comma-separated list. The database sorts by the first column, and only uses the second to order rows that tie on the first, and so on:
SELECT first_name, department_id, salary
FROM employees
WHERE department_id IN (1, 2)
ORDER BY department_id, salary DESC;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
| Rohan | 1 | 82000 |
| Isha | 1 | 78000 |
| Diya | 1 | 60000 |
| Vikram | 2 | 150000 |
| Neha | 2 | 90000 |
| Sameer | 2 | 90000 |
| Tara | 2 | 52000 |
| Aman | 2 | 50000 |
+------------+---------------+--------+Within each department, the highest salary is first. DESC applies only to the column it follows, so department_id is still ascending. If you want both descending, write DESC twice.
Ties and a reliable order
When several rows have the same sort value, the database may return them in any order. Arjun and Kavya both earn 125000. Sorting by salary alone does not say who comes first, and the answer could change between runs or between databases. Add a tie-breaker, ideally a column that is unique, such as id or a name:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name
LIMIT 5;+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Meera | Iyer | 180000 |
| Vikram | Singh | 150000 |
| Kavya | Nair | 125000 |
| Arjun | Rao | 125000 |
| Lata | Bose | 115000 |
+------------+-----------+--------+Now the order is fully determined: Nair comes before Rao alphabetically, so Kavya is listed before Arjun. A fully deterministic order is essential when you page through results in the next lesson.
Sorting by expressions and aliases
You can sort by a calculation, and ORDER BY can also use an alias from the select list. That works because sorting happens after the select list is evaluated:
SELECT first_name, salary * 12 AS yearly
FROM employees
ORDER BY yearly DESC
LIMIT 3;+------------+---------+
| first_name | yearly |
+------------+---------+
| Meera | 2160000 |
| Vikram | 1800000 |
| Arjun | 1500000 |
+------------+---------+You can also sort by an expression that is not in the select list at all. This lists the shortest last names first, then alphabetical order:
SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(last_name), last_name
LIMIT 4;+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Rohan | Das |
| Arjun | Rao |
| Lata | Bose |
| Meera | Iyer |
+------------+-----------+LENGTH is available on MySQL, PostgreSQL and SQLite. SQL Server calls it LEN.
Sorting by position
SQL allows a column number instead of a name: ORDER BY 2 sorts by the second item in the select list.
SELECT first_name, last_name
FROM employees
ORDER BY 2
LIMIT 3;+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Lata | Bose |
| Diya | Chopra |
| Rohan | Das |
+------------+-----------+It works, and you will see it in other people's quick queries, but it breaks silently when someone reorders the select list. Prefer names in code that will live a while.
Where NULLs go
NULL is not larger or smaller than other values, so databases must choose where it goes. SQLite and MySQL put NULLs first in ascending order and last in descending order. PostgreSQL does the reverse. SQL Server puts NULLs first in ascending order, like MySQL.
SELECT first_name, manager_id
FROM employees
WHERE id IN (1, 2, 3, 18)
ORDER BY manager_id;+------------+------------+
| first_name | manager_id |
+------------+------------+
| Meera | NULL |
| Ravi | NULL |
| Arjun | 1 |
| Kavya | 1 |
+------------+------------+This is the SQLite result, and the two people without a manager come first. PostgreSQL would list them last. The standard has NULLS FIRST and NULLS LAST, which PostgreSQL and SQLite (3.30 and later) support, but MySQL and SQL Server do not:
SELECT first_name, manager_id
FROM employees
WHERE id IN (1, 2, 3, 18)
ORDER BY manager_id NULLS LAST;+------------+------------+
| first_name | manager_id |
+------------+------------+
| Arjun | 1 |
| Kavya | 1 |
| Meera | NULL |
| Ravi | NULL |
+------------+------------+If you need the same behaviour on every database, sort first by a test, for example ORDER BY CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END, manager_id. The lesson on CASE explains this form.
How text is sorted
Text sorts by the database's collation, which controls case and accents. In some setups uppercase letters sort before all lowercase ones, and in others they are mixed together. Numbers stored as text sort as text, so '10' comes before '9'. Keep numbers in numeric columns.
Differences between databases
- NULL position differs, as above, and
NULLS FIRST/LASTis not available on MySQL or SQL Server. LENGTHversusLENfor text length.- Collation decides how letters of different cases and accents sort.
- On SQL Server, a query that uses
OFFSET/FETCHorTOPfor paging should have anORDER BY. In a subquery or view, SQL Server also requiresTOPorOFFSETto allow anORDER BY.
For a MySQL view, see ORDER BY and LIMIT.
Common mistakes
- Relying on the "natural" order of a table without an
ORDER BY. - Forgetting
DESCwhen you wanted the top values first. - Assuming
DESCapplies to every column in the list. It only affects the column it follows. - No tie-breaker when paging, which makes rows appear on two pages or none.
ORDER BYa column number that no longer matches after the select list changes.
Exercise
List employees in department 1 from newest hire to oldest, showing first_name, job_title and hire_date.
Show one possible answer
SELECT first_name, job_title, hire_date
FROM employees
WHERE department_id = 1
ORDER BY hire_date DESC;+------------+----------------------+------------+
| first_name | job_title | hire_date |
+------------+----------------------+------------+
| Diya | Junior Developer | 2024-06-10 |
| Isha | Developer | 2022-09-05 |
| Rohan | Developer | 2021-06-14 |
| Kavya | Senior Developer | 2020-01-20 |
| Arjun | Senior Developer | 2019-07-01 |
| Meera | Engineering Director | 2018-03-12 |
+------------+----------------------+------------+WHERE comes before ORDER BY in the query. Dates in YYYY-MM-DD form sort correctly because the year comes first.
Key points
- Without ORDER BY the order of rows is not guaranteed, even if it looks stable today.
- ORDER BY column sorts ascending by default. Add DESC for descending order.
- List several columns to break ties: ORDER BY department_id, salary DESC.
- You can sort by an alias or an expression, and ORDER BY runs after the select list is built.
- Databases disagree on where NULLs sort, so state your intention if NULLs are possible.