ORDER BY, LIMIT and OFFSET: sorting and top N

ORDER BY sorts the result by one or more columns (ASC for smallest first, DESC for largest first), and LIMIT keeps only the first n rows, with OFFSET to skip some first.

Why it matters

"The 3 highest salaries", "the newest orders first", "page 2 of the results" — order matters to people. And without ORDER BY, the database may return rows in any order it likes, which can change from one run to the next.

The idea

ORDER BY salary DESC sorts biggest first. Add more columns to break ties: ORDER BY salary DESC, name sorts by salary, and people with the same salary by name. ASC (smallest first) is the default.

LIMIT 3 keeps the first 3 rows after sorting. OFFSET 3 skips 3 rows first, which is how page 2 works when there are 3 rows per page. Without an ORDER BY, LIMIT just takes whichever rows come first — an arbitrary choice.

A race. ORDER BY lines the runners up by finishing time; LIMIT 3 hands out gold, silver and bronze. OFFSET 3 LIMIT 3 is "who came 4th, 5th and 6th?".

In a race, two runners on the same time share a place. LIMIT doesn't: it cuts at exactly n rows, so if 3rd and 4th place tie, one of them is dropped without warning. Unit 5 (RANK) handles ties properly.

Syntax

SELECT columns
FROM table_name
ORDER BY column1 DESC, column2 ASC
LIMIT n OFFSET skip;

How it works

  1. The database finds all the rows first (FROM, then WHERE).
  2. ORDER BY salary DESC puts the highest salary first: Asha 95000, Hana 80000, then Ben and Chen on 70000…
  3. Ben and Chen tie on salary, so the second sort column, name, decides: Ben before Chen.
  4. LIMIT 3 keeps the first three rows: Asha, Hana, Ben. Chen is cut off even though he earns the same as Ben.
  5. OFFSET 3 LIMIT 3 would skip those three and return the next three.

Key points

Common mistakes

Not true: LIMIT 3 always returns all of the three highest salaries, even when several people earn the same.
In fact: With a tie at the cut-off, LIMIT 3 keeps three rows and silently drops the others on the same salary. Ask how ties should count before you answer.

In the interview

ORDER BY sorts the final result, with ASC or DESC per column, and I always add a tiebreaker column so the order is deterministic. LIMIT and OFFSET then cut the sorted result. I point out two caveats. LIMIT ignores ties, so "top 3" may need RANK instead. And large OFFSETs are slow, so for deep pagination I use keyset pagination: WHERE id greater than the last id seen.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.