ORDER BY, LIMIT and OFFSET: sorting and top N

Step 1 Understand it

Why you need it. "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.

Watch it happen

Picture it

flowchart LR
  A["rows in any order"] -->|"ORDER BY salary DESC, name"| B["1 Asha 95000<br/>2 Hana 80000<br/>3 Ben 70000<br/>4 Chen 70000<br/>5 Dara 60000 …"]
  B -->|"LIMIT 3"| C["Asha, Hana, Ben"]

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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?".

Where the analogy breaks

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. Phase 5 (RANK) handles ties properly.

Worked examples

SELECT name, salary
FROM employees
ORDER BY salary DESC, name;

SELECT name, salary
FROM employees
ORDER BY salary DESC, name
LIMIT 3;

SELECT order_id, order_date
FROM orders
ORDER BY order_date
LIMIT 3 OFFSET 3;

SELECT name, city
FROM customers
ORDER BY city, name;

Must-know points

• Without ORDER BY, row order is not guaranteed — even if it looks sorted today.
• ASC (default) is smallest first; DESC is largest first. Each column gets its own direction.
• Add a second sort column to break ties, so the order is the same every time.
• LIMIT cuts after sorting. It ignores ties.
• OFFSET skips rows; it is fine for small pages but slow for deep pages, because the skipped rows are still read.

Common mistakes

• Using LIMIT without ORDER BY and expecting the "top" rows.
• Writing ORDER BY salary, name DESC and expecting both to be descending. DESC applies only to the column right before it.
• Answering "top 3 salaries" with LIMIT 3 when there are ties.

In other databases

• SQL Server uses SELECT TOP 3 …, or OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY. Oracle and the SQL standard use FETCH FIRST 3 ROWS ONLY. PostgreSQL, MySQL and SQLite use LIMIT.
• NULL position: PostgreSQL and Oracle put NULLs last for ASC; SQLite, MySQL and SQL Server put them first. PostgreSQL and SQLite accept NULLS FIRST / NULLS LAST.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL ORDER BY Keyword

  short page with a try-it editor, free

• Read

  PostgreSQL docs — LIMIT and OFFSET

  official documentation, free

• Practice

  SQLBolt — Lesson 4: Filtering and sorting Query results

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why "the 3 highest-paid employees" with LIMIT 3 can be a wrong answer here.

   Show answer

   Because of a tie. Asha (95,000) and Hana (80,000) are first and second, and Ben and Chen both earn 70,000, so they share third place.

   ORDER BY salary DESC LIMIT 3 must cut one of them, and which one depends on the tie-breaker. With no tie-breaker it is whichever the database happens to return first. Either way you leave out someone who is just as highly paid.

   So first ask: should ties be included? If yes, the answer has 4 people. Use RANK or DENSE_RANK (Phase 5), or WHERE salary >= the third-highest salary. If no, say how you break the tie, for example ORDER BY salary DESC, name.

2. Writefrom a blank page, then run it

   Write a query for the 2 most expensive products, showing product_name and price.

   Show answer

   Sort from most expensive and keep 2. The next product is the £25 desk lamp, so there is no tie to worry about here (but always check for one).

   sqlCopy

   SELECT product_name, price
   FROM products
   ORDER BY price DESC
   LIMIT 2;

   Try it

   2 rows

   product_name	price
   Headphones	40
   Backpack	30

3. Predictsay it before you run it

   What does SELECT name FROM employees ORDER BY hire_date DESC LIMIT 2; return?

   Show answer

   Gus, then Hana: the two most recent hires. DESC puts the latest date first. Gus joined on 2023-04-03 and Hana on 2022-11-30. Dates in YYYY-MM-DD form sort correctly even though they are text.

   sqlCopy

   SELECT name, hire_date
   FROM employees
   ORDER BY hire_date DESC
   LIMIT 2;

   Try it

   2 rows

   name	hire_date
   Gus	2023-04-03
   Hana	2022-11-30

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Write a query that lists customers by city, with the customer who has no city at the end, not the start.

   Show answer

   SQLite puts NULLs first when sorting A to Z, so Zoe would come first. Sort by "is the city missing?" first: city IS NULL is 0 for a real city and 1 for NULL, so the missing one goes last.

   SQLite and PostgreSQL also accept ORDER BY city NULLS LAST, name, which gives the same rows. The IS NULL trick also works in MySQL, which has no NULLS LAST.

   sqlCopy

   SELECT name, city
   FROM customers
   ORDER BY city IS NULL, city, name;

   Try it

   6 rows

   name	city
   Omar	Bristol
   Tom	Leeds
   Tom	Leeds
   Lena	London
   Priya	London
   Zoe	NULL

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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.

Follow-up questions

Tick each check when you can do it without notes