ORDER BY and LIMIT

Concepts covered: Sorting Results, Row Limiting

A query without ORDER BY returns rows in whatever order the engine found cheapest, and that order isn't stable: it can change when someone adds an index or the plan is rewritten, and another replica may return a different one. ORDER BY makes the order part of the result. LIMIT keeps the first N rows of that order, and the pair answers every question of the form 'the top 5' or 'the 20 most recent', which is a large share of what a dashboard runs.

Sorting by one column or several

ORDER BY takes a list of expressions, each with its own direction. ASC is the default and sorts smallest first, DESC largest first. With 2 columns the second decides the order only among rows that tie on the first: ORDER BY country, total_spent DESC keeps each country's customers together and ranks them by spend inside the country. Any expression works, including an alias from SELECT, which is available because sorting is step 7 and aliases are born at step 6.

Top N

The animation sorts 6 customers by total_spent descending and keeps 3.

SELECT name, total_spent
FROM customers
ORDER BY total_spent DESC
LIMIT 3

Eve (1500), Ben (1250) and Cara (980) come out, in that order. The engine does not need to finish sorting the other 3 to produce them: a LIMIT above a sort lets the planner keep only the 3 largest values seen so far in a bounded heap, which is why ORDER BY ... LIMIT 10 over 100 million rows finishes in seconds while a bare ORDER BY spills the whole table to disk. The switcher below runs 3 formulations over the same rows.

Top 3

Sort by spend, keep the 3 largest

SELECT name, total_spent
FROM customers
ORDER BY total_spent DESC
LIMIT 3

No ORDER BY

Keep 3 rows with no order specified

SELECT name, total_spent
FROM customers
LIMIT 3

Page 2

Skip the first page, then keep 3

SELECT name, total_spent
FROM customers
ORDER BY total_spent DESC
LIMIT 3 OFFSET 3

The second tab drops the ORDER BY, and the 3 rows it returns are whichever 3 the engine read first, the stored order on a table this small. Eve, the top spender, is absent. On a production table the set changes from run to run, and a report built on it goes wrong silently, because 3 rows did come back. LIMIT without ORDER BY is a sample, never a ranking.

Paging with OFFSET

The third tab adds OFFSET 3: skip 3 rows of the sorted result, then take 3, so page 2 holds Finn (760), Ann (420) and Dev (310). Page k of size n is LIMIT n OFFSET (k - 1) * n. The engine still sorts and then discards the skipped rows, so page 1,000 at 50 rows a page is a 50,000-row sort and discard, and every later page costs more. Ties break the pages too: with 2 customers both at 760 and only ORDER BY total_spent, the engine may put either first on each run, so a customer can appear on page 2 and again on page 3, or on neither.

The fix for ties is a unique tiebreaker column, almost always the primary key: ORDER BY total_spent DESC, id. The fix for deep pages is keyset pagination, which remembers the last row of the previous page and asks for the rows after it, so the engine seeks to that row instead of sorting and discarding everything above it.

SELECT name, total_spent, id
FROM customers
WHERE total_spent < 760
   OR (total_spent = 760 AND id > 6)
ORDER BY total_spent DESC, id
LIMIT 3

NULL has no natural position in an ordering, and engines disagree: PostgreSQL sorts NULLs last ascending and first descending. MySQL and SQLite do the reverse. NULLS FIRST and NULLS LAST pin it where the engine supports them.

Do

• Name every CTE for what 1 of its rows represents
• Read a failing query in execution order, FROM first, SELECT last
• Default to UNION ALL and reach for UNION only when duplicates are wrong
• Pair every LIMIT with an ORDER BY that ends in a unique column
• Test a stage alone by selecting from its CTE name

Avoid

• Reference a SELECT alias in WHERE or HAVING, or group by it
• Copy the same subquery into 2 places in 1 query
• Rely on column names to line up a UNION; position is all that matters
• Page deep into a result with OFFSET when a keyset condition would seek
• Treat LIMIT without ORDER BY as the top of anything

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.