Top N Queries

Definition

A "Top N" query answers: "give me the N highest (or lowest) rows by some measure, across the whole result set." The standard pattern is ORDER BY on the measure, then LIMIT N:

sql
SELECT column1, measure_column
FROM table_name
ORDER BY measure_column DESC
LIMIT n;

Worked Example: Top 3 Products by Rating

sql
SELECT name, rating
FROM products
ORDER BY rating DESC
LIMIT 3;

Result:

 name               | rating
--------------------+--------
 Yoga Mat           | 4.6
 Bluetooth Speaker  | 4.5
 Table Lamp         | 4.3

Bottom N

Simply flip the direction:

sql
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;   -- 3 cheapest products

Handling Ties at the Cutoff

A subtlety: if the Nth and (N+1)th rows are tied on the sort value, plain LIMIT N arbitrarily includes one and excludes the other (whichever the engine happens to pick), which can feel wrong for a "Top N" ranking that users expect to be fair. Some engines offer FETCH FIRST N ROWS WITH TIES (SQL Server, and standard SQL in newer engines) to include all rows tied with the Nth value, potentially returning more than N rows. Plain LIMIT does not have this option — it always returns exactly N (or fewer) regardless of ties.

Top N Per Group — A Different, Harder Problem

A plain "Top N" query answers a global question — the top N rows across the entire table. A very different and much harder question is "Top N per group" — for example: "show me the top 2 highest-rated products within each category."

sql
-- This does NOT give top-2-per-category; it just gives the global top 2:
SELECT name, category, rating FROM products ORDER BY rating DESC LIMIT 2;

That query would return only 2 rows total, possibly both from the same category, which is not what "top 2 per category" means. Solving "Top N per group" correctly requires window functions (ROW_NUMBER(), RANK(), or DENSE_RANK() partitioned by category) — a technique covered later, in the chapter on window functions (Chapter 33). For now, just recognize the shape of the problem and know that plain ORDER BY ... LIMIT N is not the tool for it.

Key Takeaways

• Q: What is the standard pattern for a global Top N query?

A: ORDER BY measure DESC LIMIT N (or ASC for Bottom N).

• Q: Does `LIMIT N` guarantee exactly N rows if there's a tie at the boundary?

A: No — plain LIMIT cuts off at exactly N rows even mid-tie; some engines offer WITH TIES to include tied rows beyond N.

• Q: Can `ORDER BY ... LIMIT N` solve "Top N per group"?

A: No — that needs window functions (covered later), since LIMIT only caps the overall result, not per-group subsets.