Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 10 of 49

SQL ORDER BY: sorting

Sort query results with ORDER BY, in ascending or descending order, by several columns, by aliases and expressions, and learn how NULLs and ties are handled.

12 minFiltering and sortingUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonSort results by one or several columns in either direction, and explain how ties and NULLs are placed.

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

sql
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date
LIMIT 4;
result
+------------+------------+
| 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:

sql
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
result
+------------+--------+
| 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:

sql
SELECT first_name, department_id, salary
FROM employees
WHERE department_id IN (1, 2)
ORDER BY department_id, salary DESC;
result
+------------+---------------+--------+
| 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:

sql
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name
LIMIT 5;
result
+------------+-----------+--------+
| 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:

sql
SELECT first_name, salary * 12 AS yearly
FROM employees
ORDER BY yearly DESC
LIMIT 3;
result
+------------+---------+
| 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:

sql
SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(last_name), last_name
LIMIT 4;
result
+------------+-----------+
| 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.

sql
SELECT first_name, last_name
FROM employees
ORDER BY 2
LIMIT 3;
result
+------------+-----------+
| 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.

sql
SELECT first_name, manager_id
FROM employees
WHERE id IN (1, 2, 3, 18)
ORDER BY manager_id;
result
+------------+------------+
| 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:

sql
SELECT first_name, manager_id
FROM employees
WHERE id IN (1, 2, 3, 18)
ORDER BY manager_id NULLS LAST;
result
+------------+------------+
| 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/LAST is not available on MySQL or SQL Server.
  • LENGTH versus LEN for text length.
  • Collation decides how letters of different cases and accents sort.
  • On SQL Server, a query that uses OFFSET/FETCH or TOP for paging should have an ORDER BY. In a subquery or view, SQL Server also requires TOP or OFFSET to allow an ORDER 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 DESC when you wanted the top values first.
  • Assuming DESC applies 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 BY a 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
sql
SELECT first_name, job_title, hire_date
FROM employees
WHERE department_id = 1
ORDER BY hire_date DESC;
result
+------------+----------------------+------------+
| 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.

Check yourself

1. A query has no ORDER BY. In what order are the rows returned?
2. What does ORDER BY department_id, salary DESC do?
3. Which statement about aliases and ORDER BY is true?
4. Two employees both earn 125000. Why add a second sort column?