SQL Academy · Lesson

Finding Top-N Records

Get the highest and lowest values.

Finding Top-N Records is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Top-N Query

A Top-N query answers questions like "the 3 most expensive products", "the 10 newest orders" or "the lowest 5 scores".

It's just sorting plus a limit, but it's so common it deserves a name.

-- Top 3 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Bottom-N Records

For the smallest values, flip the direction. Sort ASC and take the first N to get the cheapest, oldest or lowest rows.

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

Adding a Tiebreaker

If several rows share the cutoff value, the database picks arbitrarily among them. Add a unique secondary key so the result is stable and reproducible.

SELECT id, name, price
FROM products
ORDER BY price DESC, id
LIMIT 3;

Top-N After Filtering

Top-N works on the filtered set. Put a WHERE clause first to restrict the candidates, then sort and limit.

This finds the 3 most expensive products in the 'Books' category.

SELECT name, price
FROM products
WHERE category = 'Books'
ORDER BY price DESC
LIMIT 3;

Top-N After Aggregation

You can rank computed values too. Group, aggregate, then sort the aggregate and limit.

This returns the 5 customers who spent the most overall.

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC
LIMIT 5;

The Ranking Functions Approach

Window functions like ROW_NUMBER() assign a rank to each row. Wrap the query and keep rows where the rank is small. This scales to "Top-N per group".

SELECT name, price
FROM (
  SELECT name, price,
         ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

RANK vs ROW_NUMBER for Ties

ROW_NUMBER() always gives distinct numbers, so it returns exactly N rows even on ties. RANK() gives tied rows the same number, so "top 3" may return more than 3 if there's a tie at third place.

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS rnk
FROM products;

Top-N Per Group

To get the top product in each category, partition the ranking by category. PARTITION BY restarts the numbering for every group.

SELECT category, name, price
FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC
         ) AS rn
  FROM products
) t
WHERE rn = 1;

DISTINCT ON for One Row Per Group

PostgreSQL's DISTINCT ON is a compact way to pick the top row per group. List the grouping column, then order so the wanted row comes first within each group.

SELECT DISTINCT ON (category)
       category, name, price
FROM products
ORDER BY category, price DESC;

Including Ties with WITH TIES

If you want the top 3 but also any rows tied with the 3rd, use the standard FETCH ... WITH TIES. It returns 3 rows, plus extras that tie the last one.

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;

Single Top Value

For just the single highest or lowest row, ORDER BY ... LIMIT 1 is the simplest pattern, clearer and often faster than a subquery with MAX().

-- The single most recent order
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 1;

Quick Check

You want the top 3 prices, but if rows tie at 3rd place you want all of them included.

Recap

You can now find Top-N records every way that counts:

ORDER BY ... LIMIT n for simple top/bottom lists
Add a unique tiebreaker for stable results
ROW_NUMBER() or DISTINCT ON for Top-N per group
FETCH ... WITH TIES to keep tied rows

That completes sorting, limiting and Top-N. You can now bring real order to any result set.

SELECT name, price
FROM products
ORDER BY price DESC, id
LIMIT 3;

Frequently asked questions

Is the “Finding Top-N Records” lesson free?

Yes — the full text of “Finding Top-N Records” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Finding Top-N Records”?

Get the highest and lowest values. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Finding Top-N Records” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy