ORDER BY and LIMIT: sorting and limiting

“The five students with the highest scores”, “the cheapest products”, “page 2 of the search results” — all of these take the same two steps: first sort the rows, then take as many as you need. In SQL this is done by ORDER BY and LIMIT.

ORDER BY: sorting

ORDER BY goes at the end of the query. By default the order is ascending (ASC: smallest to largest, A to Z). For descending order, write DESC after the column. Remember: without ORDER BY the DBMS may return rows in any order it likes.

SELECT name, price
FROM products
ORDER BY price DESC;

name | price
Laptop | 1450
Smartphone | 899.99
Monitor | 310
Headphones | 120.5
Backpack | 55
Chess set | 42
Desk lamp | 34.99
Water bottle | 12
Pen set | 7.9
Notebook | 3.2

You can also sort by several columns. The second column only matters when the values in the first one are equal — just as a dictionary sorts words by the first letter, then by the second. Below, students are sorted by age descending, and students of the same age by first name ascending. Each column has its own direction.

SELECT first_name, age, grade
FROM students
ORDER BY age DESC, first_name ASC;

first_name | age | grade
Elvin | 17 | 11
Fidan | 17 | 11
Tural | 17 | 11
Murad | 16 | 10
Rəşad | 16 | 10
Səbinə | 16 | 10
Aysel | 15 | 9
Kamran | 15 | 9
Nigar | 15 | 9
Günay | 14 | 8
Leyla | 14 | 8
Orxan | 14 | 8

LIMIT and OFFSET: top N rows and pages

LIMIT n keeps only the first n rows of the result. Together with ORDER BY it becomes a “top N” query. Below we find the three products with the largest stock value — sorting directly by the stock_value alias.

SELECT name, price, stock,
       price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 3;

name | price | stock | stock_value
Smartphone | 899.99 | 15 | 13499.85
Laptop | 1450 | 8 | 11600
Headphones | 120.5 | 40 | 4820

SELECT student_id, course_id, score
FROM enrollments
ORDER BY score DESC
LIMIT 5;

student_id | course_id | score
6 | 6 | 99
11 | 1 | 97
3 | 2 | 95
11 | 3 | 94
5 | 5 | 93

OFFSET m skips the first m rows. This is how websites build pagination: with n rows per page, page k is LIMIT n OFFSET (k − 1) · n. With three products per page, page 2 shows the 4th, 5th and 6th cheapest products.

-- page 2, three products per page
SELECT name, price
FROM products
ORDER BY price
LIMIT 3 OFFSET 3;

name | price
Desk lamp | 34.99
Chess set | 42
Backpack | 55

NULL and national letters

Where does NULL go when sorting? In SQLite, MySQL and SQL Server NULL values come first in ascending order; in PostgreSQL they come last. SQLite and PostgreSQL let you choose yourself: NULLS FIRST or NULLS LAST.

SELECT first_name, email
FROM students
ORDER BY email, id
LIMIT 4;

first_name | email
Elvin | NULL
Tural | NULL
Aysel | aysel@example.com
Fidan | fidan@example.com

SELECT first_name, email
FROM students
ORDER BY email NULLS LAST, id
LIMIT 4;

first_name | email
Aysel | aysel@example.com
Fidan | fidan@example.com
Günay | gunay@example.com
Kamran | kamran@example.com

SELECT first_name, last_name
FROM students
ORDER BY last_name;

first_name | last_name
Günay | Abbasova
Orxan | Babayev
Fidan | Cəfərova
Leyla | Hüseynova
Kamran | Həsənov
Rəşad | Kərimov
Aysel | Məmmədova
Səbinə | Nəsirova
Elvin | Quliyev
Nigar | Rzayeva
Tural | İsmayılov
Murad | Əliyev

Look at the end of the result: İsmayılov and the surname starting with the letter schwa (an upside-down e) come after Rzayeva, although in the Azerbaijani alphabet both letters come much earlier. The reason: by default SQLite compares letters by their character codes, and special letters have larger codes than A–Z.

Dialects: LIMIT, TOP and FETCH

LIMIT works in SQLite, MySQL and PostgreSQL. In SQL Server you write TOP n before the columns instead. The SQL standard has the OFFSET ... FETCH syntax, which PostgreSQL and SQL Server (since version 2012) understand. The two queries below do not run in SQLite, but they return the same result as a LIMIT 3 query.

-- SQL Server
SELECT TOP 3 name, price
FROM products
ORDER BY price DESC;

-- SQL standard: PostgreSQL, SQL Server 2012+
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

name | price
Laptop | 1450
Smartphone | 899.99
Monitor | 310

SELECT name, price
FROM products;

name | price
Notebook | 3.2
Pen set | 7.9
Water bottle | 12

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 4;

name | price
Backpack | 55
Chess set | 42
Desk lamp | 34.99
Water bottle | 12