Half the questions a stakeholder asks start with the word 'top'. Top 10 products. Top 5 customers. Worst 3 regions. Those are two clauses in SQL — ORDER BY to rank, LIMIT to cut — and after this lesson they'll take you under ten seconds each.
What you'll learn
What
ORDER BY sorts the result set by one or more columns, ascending (ASC, the default) or descending (DESC). LIMIT keeps only the first N rows of that sorted result; OFFSET skips rows before counting, which is how pagination works.
Why
Tables have no inherent order — without ORDER BY, the database returns rows in whatever order is convenient for it, and that order can change between runs. Any 'top N' claim, any report sorted for humans, and any paginated API needs explicit sorting to be correct and reproducible.
Where it's used
Leaderboards, 'latest orders' views, best/worst-performer reports, paginated tables in every web app, and the ubiquitous 'show me the 10 biggest…' request in analytics.
Where this runs in production
Chart queries rank tracks by stream counts — ORDER BY streams DESC LIMIT 50 over a day's aggregated plays, per market.
Category pages surface the highest-selling items: sort by units sold descending, cut to the top few, refreshed on a schedule.
The 'your orders' screen pages through past orders newest-first — ORDER BY created_at DESC with LIMIT/OFFSET (or a keyset variant) per page.
A sort with multiple keys works like a librarian shelving books: first by author surname (primary key), and when two books share an author, by title (tie-breaker). ORDER BY country, amount DESC does the same — rows are grouped alphabetically by country, and within each country the biggest amounts come first.
A table is a set of rows, not a sequence. Without ORDER BY the database may return rows in insertion order, index order, or parallel-scan order — and it can differ run to run. If row order matters even slightly (reports, LIMIT, exports, tests), ORDER BY is mandatory, not decorative.
SELECT product_name,
quantity * unit_price AS line_total
FROM order_items
ORDER BY line_total DESC;Unlike WHERE, ORDER BY runs after the SELECT list — so it CAN reference output aliases like line_total. You can also sort by expressions directly (ORDER BY quantity * unit_price DESC). Some dialects allow ORDER BY 2 (sort by the second output column); it works, but named keys survive column reordering and are the professional habit.
LIMIT 10 on an unsorted query returns ten arbitrary rows — not the newest, not the biggest, just whatever the engine produced first. Every LIMIT should sit on top of an ORDER BY that defines what 'first' means. This is the most common junior-analyst bug in shared dashboards.
If your sort key has ties (many orders share a date), rows within a tie can come back in any order — and pagination can show a row twice or skip it. Append a unique column as the final key: ORDER BY created_at DESC, order_id DESC. Cheap insurance, always worth it.
The pipeline position matters: sorting happens after filtering, and the cut happens last. Click each stage to see what it contributes.
List products from cheapest to most expensive.
ASC is the default, so this sorts smallest-first. The rows themselves are untouched — ORDER BY only changes presentation order, never content.
product_name | unit_price ----------------+------------ Webcam Cover | 4.50 Wireless Mouse | 25.00 Laptop Stand | 32.00 USB-C Hub | 49.50 (4 rows)
Your task
Build the 'Top spenders' widget: from orders (order_id, customer_id, amount, status), return order_id and amount for the 3 highest-value orders that are not cancelled. Biggest first; break exact-amount ties by lower order_id first. Fill in the blanks.
order_id | amount ----------+-------- 1 | 500.00 4 | 500.00 3 | 300.00 (3 rows)
Write your solution in the editor on the right, then hit Run.
What order does ORDER BY use when you write neither ASC nor DESC?
Which query returns the 5 most recent orders?
For a 25-rows-per-page UI, which clause fetches page 4?
In PostgreSQL, ORDER BY last_login DESC on a column with NULLs puts the NULL rows…
What is the root cause?
The customers table has (customer_id, name, last_purchase_date) where last_purchase_date is NULL for prospects. Return name and last_purchase_date for the 2 most recent purchasers, with prospects excluded from the top by sorting NULLs last (do not filter them out with WHERE).
Why is LIMIT without ORDER BY considered a bug in analytical queries?
Because relational tables are unordered sets: without ORDER BY, the engine returns rows in whatever order its access path produced — heap order, index order, or interleaved parallel-worker output — and that order isn't stable across runs, plans, or engine versions. LIMIT then keeps N essentially arbitrary rows while the query reads as if it returns a meaningful 'first N'. The failure mode is nasty: results look plausible, pass review, and then silently change after a vacuum, an index change, or a data reload. The rule I follow is that every LIMIT must sit on an ORDER BY that defines 'first', and if the sort key can tie, I add a unique tie-breaker so the result is fully deterministic.
Your paginated endpoint gets slow on deep pages. Why, and what would you do about it?
OFFSET-based pagination does linear work in page depth: OFFSET 100000 forces the engine to generate and discard 100,000 sorted rows before returning the page, so latency grows with depth, and concurrent inserts shift rows between pages (drift, causing duplicates or gaps). The standard fix is keyset (cursor) pagination: sort by a deterministic key like (created_at DESC, id DESC), have the client pass back the last row's key, and fetch the next page with a row-value predicate — WHERE (created_at, id) < (:last_at, :last_id) — plus the same ORDER BY and LIMIT. Each page becomes an index seek with constant cost and no drift. The trade-off is losing random page access ('jump to page 47'), which most modern infinite-scroll UIs don't need. If random access is truly required, I'd cap the browsable depth or precompute page anchors.
How do NULLs behave in ORDER BY, and how do you control it?
SQL doesn't define comparisons with NULL, so each engine picks a convention for sorting: PostgreSQL and Oracle treat NULL as the largest value (last in ASC, first in DESC), while MySQL and SQL Server treat it as the smallest. That difference bites in ranking queries — 'most recent purchase DESC' in PostgreSQL floats never-purchased customers to the top. The explicit controls are NULLS FIRST / NULLS LAST on each sort key where supported. Where they aren't, the portable idiom is to sort by an is-null flag first: ORDER BY (col IS NULL), col DESC — false sorts before true, pushing NULLs down. In interviews I'd add that this is a symptom of a broader habit: whenever a sorted column is nullable, decide deliberately where the NULLs go rather than accepting the engine default.
Common Mistakes to Avoid
1) Using LIMIT without ORDER BY and believing the rows are 'the first ones' — they're arbitrary. 2) Forgetting a unique tie-breaker, so paginated pages overlap or skip rows when sort keys tie. 3) Assuming NULLs sort the same everywhere — PostgreSQL puts them last in ASC, MySQL puts them first; pin it with NULLS FIRST/LAST. 4) Computing page offsets as page × size instead of (page − 1) × size, silently skipping the first page. 5) Sorting by a text column that holds numbers ('10' < '9' alphabetically) — cast to a numeric type before ordering.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: why do tables have no natural order?' • 'Quiz me: give me five ORDER BY clauses and small tables — I'll predict the output order.' • 'Show me a page-drift bug with OFFSET and how keyset pagination avoids it.' • 'Explain NULLS FIRST vs NULLS LAST with a leaderboard example.' • 'Interview mode: ask me to design pagination for an orders API and critique my answer.'
Glossary
ORDER BY — clause that sorts the result set by one or more keys. ASC / DESC — ascending (default) and descending sort directions. Sort key — a column or expression used for ordering. Tie-breaker — a later sort key that orders rows equal on earlier keys. Deterministic order — a total ordering that is reproducible run to run; requires a unique key. LIMIT — keeps only the first N rows of the (sorted) result. OFFSET — skips M rows before LIMIT counts. Top-N query — the ORDER BY … DESC LIMIT N idiom. Pagination — serving results page by page via LIMIT/OFFSET or keyset. Keyset (cursor) pagination — pagination that seeks past the last seen sort key instead of counting an offset. NULLS FIRST / NULLS LAST — explicit control of where missing values sort. Page drift — rows moving between pages when data changes mid-pagination.
Recommended Resources
• Docs: PostgreSQL manual — 'Sorting Rows (ORDER BY)' and 'LIMIT and OFFSET'. • Read: Use The Index, Luke's chapter on paging ('Fetching a result in chunks') for the definitive OFFSET-vs-keyset explanation. • Practice: take any orders-style table, write the top-5 query three ways (no order, DESC only, DESC + tie-breaker) and run each twice — watch which results are stable. • Next in DSM: you can rank and cut rows — Aggregate Functions teaches you to collapse them into counts, sums, and averages.
Recap
✓ Tables have no inherent order — ORDER BY is the only guarantee, and it runs after WHERE and SELECT. ✓ Multiple sort keys work primary-then-tie-breaker; add a unique final key for deterministic results. ✓ ORDER BY can reference SELECT aliases and expressions; WHERE cannot. ✓ Top-N questions are ORDER BY … DESC LIMIT N — LIMIT without ORDER BY returns arbitrary rows. ✓ NULL sort position differs by engine; pin it with NULLS FIRST / NULLS LAST. ✓ LIMIT/OFFSET paginates but slows with depth and can drift; keyset pagination seeks past the last key at constant cost. Next up: Aggregate Functions. Sorting shows you the extremes — next you'll summarize entire tables into single numbers with COUNT, SUM, AVG, MIN, and MAX, and learn how NULLs sneak into every one of them.
Run your code to see the output here.