ORDER BY, LIMIT and pages

Part of the free LogicWiz SQL & Databases course, Module 2: Your first queries. The queries and practice run in the app on real Postgres.

The college website's "Top 3 students" box shows Aditya Rao at number one. Aditya has no CGPA at all. The query was ORDER BY cgpa DESC LIMIT 3. What happened?

You'll learn to

  • Sort by one or more columns, ascending or descending
  • Control where NULLs go with NULLS FIRST / NULLS LAST
  • Take the top N with LIMIT, and show page N with OFFSET

Learn

Sorting, and the surprise

SELECT name, cgpa
FROM students
ORDER BY cgpa DESC;

ORDER BY cgpa DESC sorts from the highest CGPA down. But Aditya, with NULL, comes first.

Where NULLs go

ASC (smallest first) is the default; DESC is largest first.

Postgres sorts NULL as if it were bigger than every value. So:

  • in ASC, NULLs come last;
  • in DESC, NULLs come first. That is the Aditya bug.

You choose with NULLS FIRST or NULLS LAST after the direction: ORDER BY cgpa DESC NULLS LAST.

Fixed, with a tie-breaker

SELECT name, cgpa
FROM students
ORDER BY cgpa DESC NULLS LAST, name;

Aarav and Tanvi both have 8.7; Riya and Siddharth both have 7.9. With only cgpa in ORDER BY, the order inside a tie is not promised and can change between runs. A second key, name, settles it. Each key can have its own direction: ORDER BY year DESC, name ASC.

LIMIT: the top N

SELECT name, cgpa
FROM students
ORDER BY cgpa DESC NULLS LAST, name
LIMIT 3;

LIMIT 3 keeps the first 3 rows of the sorted answer. It runs last, after the sorting.

OFFSET: pages

-- page 2, 5 students per page
SELECT student_id, name
FROM students
ORDER BY name
LIMIT 5 OFFSET 5;

OFFSET 5 skips the first 5 rows, then LIMIT 5 keeps the next 5. For page p with n rows per page: LIMIT n OFFSET (p − 1) × n. Page 1 is OFFSET 0.

LIMIT without ORDER BY gives you some rows, not the top ones. Postgres may return them in any order, and it can change. Also make the sort complete: if the order has ties, pages can repeat or skip a row. Add a unique column (like student_id) as the last key.

MySQL sorts NULLs the other way (first in ASC, last in DESC) and has no NULLS LAST; people write ORDER BY cgpa IS NULL, cgpa DESC. MySQL also accepts LIMIT 5, 5, which means offset 5, count 5. SQL Server uses TOP 3, and standard SQL (Postgres too) has FETCH FIRST 3 ROWS ONLY.

ORDER BY runs after SELECT, so it can use a SELECT alias: SELECT cgpa * 9.5 AS pct … ORDER BY pct. WHERE runs before SELECT, so WHERE pct > 80 fails. Interviewers love this one, and the "top N without ORDER BY" mistake.

In one line

ORDER BY sorts (ASC default; Postgres puts NULLs last in ASC, first in DESC); add a tie-breaker; LIMIT n OFFSET (p−1)×n gives page p.