0Pricing
SQL Interview Prep · Lesson

Returning the Top-N Rows Reliably

Why ORDER BY plus LIMIT can be non-deterministic without a tiebreaker.

Returning the Top-N Rows Reliably is a free SQL Interview Prep lesson on CoddyKit — lesson 3 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Hidden Bug in Top-N Queries

"Give me the top 5 highest-paid employees" feels easy: ORDER BY salary DESC LIMIT 5. But interviewers plant a trap. What if six people share the same salary at the boundary? What if many rows tie?

The core issue is determinism: when the sort key has ties, LIMIT cuts arbitrarily, and the exact rows returned can change between runs. This lesson makes Top-N reliable.

Why ORDER BY + LIMIT Can Be Non-Deterministic

Consider salaries where ranks 4, 5, and 6 are all 50000. ORDER BY salary DESC LIMIT 5 must return exactly 5 rows, so it keeps two of the three tied rows and drops one, but which two is undefined.

Run the query twice, or after the optimizer changes plans, and you may get different people. That non-determinism is the bug interviewers want you to spot.

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

Fix 1: Add a Unique Tiebreaker

The simplest fix is to make the sort order total by appending a column that is unique, usually the primary key. Now no two rows are equal on the full key, so the cut is deterministic and reproducible.

This does not change which salaries appear, but it makes the choice among tied rows stable across runs.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC
LIMIT 5;

Fix 2: Include All Ties With WITH TIES

Sometimes the requirement is "include everyone tied with the cutoff," not exactly N rows. Standard SQL and SQL Server offer WITH TIES, which returns extra rows that match the last row's ORDER BY value.

If the 5th salary is shared by three people, this returns 7 rows. Note that WITH TIES requires an ORDER BY.

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

Clarify the Requirement First

Before coding, ask the interviewer: "If there are ties at the boundary, do you want exactly N rows or all tied rows?" This single clarifying question shows seniority.

  • Exactly N, stable: add a unique tiebreaker.
  • All ties included: use WITH TIES or RANK.
  • Distinct values: use DENSE_RANK.

The Portable Window-Function Approach

Many engines lack WITH TIES. The portable, powerful pattern uses a ranking window function in a subquery or CTE, then filters by the rank. ROW_NUMBER gives exactly N rows with a deterministic order key.

You must wrap the window function because you cannot reference it directly in WHERE.

SELECT name, salary
FROM (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 5;

RANK to Keep Ties

Swap ROW_NUMBER for RANK when you want all tied rows kept and gaps in the numbering. If three rows tie for rank 4, they all get rank 4 and the next rank is 7.

Filtering rank <= 5 then returns every row in the top five salary positions, ties included.

SELECT name, salary
FROM (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 5;

DENSE_RANK for Top-N Distinct Values

"Top 3 salary levels" (not top 3 people) means distinct values. DENSE_RANK assigns the same rank to ties and does not skip numbers, so dense_rnk <= 3 returns everyone earning one of the three highest distinct salaries.

Knowing which ranking function answers which phrasing is a classic differentiator.

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
) ranked
WHERE drnk <= 3;

Top-1 Special Case

For the single top row, ORDER BY ... LIMIT 1 works but still risks ties. If you want every row that holds the maximum, compare against a subquery max, or use RANK() = 1.

The max-subquery form is clean and runs in any dialect.

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Comparing the Approaches

Summary of when to use each tool for reliable Top-N:

  • LIMIT + unique tiebreaker: exactly N rows, stable, simplest.
  • FETCH ... WITH TIES: exactly N plus boundary ties, standard SQL.
  • ROW_NUMBER: exactly N, deterministic, fully portable.
  • RANK: top N positions including all ties.
  • DENSE_RANK: top N distinct values.

Top-N Per Group Preview

The window approach generalizes beautifully. Add PARTITION BY to get the top N within each group, for example the top 2 earners per department. The same rn <= n filter applies after partitioning.

This per-group Top-N is one of the highest-frequency real interview problems, built on exactly the pattern you just learned.

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

Quick Check

Match the requirement to the right function.

Recap

To return Top-N reliably:

  • ORDER BY ... LIMIT alone is non-deterministic when the sort key has ties.
  • Add a unique tiebreaker for stable exactly-N results.
  • Use WITH TIES or RANK to keep boundary ties.
  • Use DENSE_RANK for top-N distinct values.
  • Always clarify whether the interviewer wants exactly N rows or all ties.

Frequently asked questions

Is the “Returning the Top-N Rows Reliably” lesson free?

Yes — the full text of “Returning the Top-N Rows Reliably” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Returning the Top-N Rows Reliably”?

Why ORDER BY plus LIMIT can be non-deterministic without a tiebreaker. You practise SQL Interview Prep 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 Interview Prep?

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

How long does the “Returning the Top-N Rows Reliably” 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 Interview Prep lesson?

Yes. Every SQL Interview Prep 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

  1. Multi-Column Sorting and NULL Placement
  2. LIMIT, OFFSET and FETCH FIRST
  3. Returning the Top-N Rows Reliably
  4. Sorting by Expressions and Aliases
← Back to SQL Interview Prep