ROW_NUMBER vs RANK vs DENSE_RANK: Top-N SQL Interview Patterns and Tie Rules

Introduction

ROW_NUMBER, RANK, and DENSE_RANK all assign positions inside a window. They are often taught together, so candidates start treating them as minor syntax variants. In interviews, that is exactly where the wrong answer begins.

The real question is not "which ranking function do you remember?" The real question is: what should happen when rows tie?

If the business needs exactly two brands per category, use ROW_NUMBER and define a stable tie-breaker. If the business wants everyone tied for second place, use a rank function that preserves ties. If the business wants the second distinct value, DENSE_RANK is usually the cleanest fit.

The Sample Data

Use this table for the examples:

-- brand_sales
-- category | brand  | total_sales
-- shoes    | Nike   | 100
-- shoes    | Adidas | 100
-- shoes    | NewCo  | 90
-- shoes    | Other  | 70
-- shirts   | Polo   | 120
-- shirts   | Basic  | 80
-- shirts   | Value  | 80
-- shirts   | Other  | 40

There are ties in both categories. That is not an edge case. It is the point of the exercise.

The Difference in One Result Table

SELECT
  category,
  brand,
  total_sales,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY total_sales DESC, brand ASC
  ) AS row_num,
  RANK() OVER (
    PARTITION BY category
    ORDER BY total_sales DESC
  ) AS rank_num,
  DENSE_RANK() OVER (
    PARTITION BY category
    ORDER BY total_sales DESC
  ) AS dense_rank_num
FROM brand_sales
ORDER BY category, total_sales DESC, brand ASC;

For the shoes category, the output is:

ROW_NUMBER creates a unique sequence. RANK gives tied rows the same rank and leaves a gap. DENSE_RANK gives tied rows the same rank without leaving a gap.

ROW_NUMBER: Exactly N Rows Per Group

Use ROW_NUMBER when the result must contain exactly N rows per group:

WITH ranked_brands AS (
  SELECT
    category,
    brand,
    total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY total_sales DESC, brand ASC
    ) AS rn
  FROM brand_sales
)
SELECT category, brand, total_sales
FROM ranked_brands
WHERE rn <= 2
ORDER BY category, rn;

Expected result for shoes:

The brand ASC tie-breaker makes the output deterministic. Without it, two tied brands can appear in either order. That may not matter for a quick exploration, but it does matter for production tables, tests, and interview explanations.

For deduplication problems, ROW_NUMBER is also the usual choice because downstream logic often expects one row per key. For more on that pattern, read How to Remove Duplicates in SQL: DISTINCT vs ROW_NUMBER.

RANK: Competition Ranking With Gaps

RANK is the competition-ranking function:

WITH ranked_brands AS (
  SELECT
    category,
    brand,
    total_sales,
    RANK() OVER (
      PARTITION BY category
      ORDER BY total_sales DESC
    ) AS sales_rank
  FROM brand_sales
)
SELECT category, brand, total_sales, sales_rank
FROM ranked_brands
WHERE sales_rank <= 2
ORDER BY category, sales_rank, brand;

Expected result for shoes:

NewCo has rank 3 because rank 2 was skipped after the first-place tie. That is correct for competition ranking. It is wrong if the prompt means top two distinct sales values.

DENSE_RANK: Top N Distinct Values

DENSE_RANK is usually the best answer when the prompt asks for the Nth highest distinct value:

WITH ranked_brands AS (
  SELECT
    category,
    brand,
    total_sales,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY total_sales DESC
    ) AS dense_sales_rank
  FROM brand_sales
)
SELECT category, brand, total_sales, dense_sales_rank
FROM ranked_brands
WHERE dense_sales_rank <= 2
ORDER BY category, dense_sales_rank, brand;

Expected result for shoes:

This returns more than two rows because the top distinct value has a tie. That is the right behavior if the business requested top two distinct sales values, not exactly two brands.

Aggregate Before Ranking

Many interview prompts say "top brands by sales," but the raw table is really transactions:

-- sales_events
-- category | brand | order_id | sales_amount

If you rank this raw table directly, you rank orders, not brands. Aggregate to the ranking grain first:

WITH brand_sales AS (
  SELECT
    category,
    brand,
    SUM(sales_amount) AS total_sales
  FROM sales_events
  GROUP BY category, brand
),
ranked_brands AS (
  SELECT
    category,
    brand,
    total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY total_sales DESC, brand ASC
    ) AS rn
  FROM brand_sales
)
SELECT category, brand, total_sales
FROM ranked_brands
WHERE rn <= 2;

This same grain-first habit applies to latest-row-per-group problems. If that is the prompt, use SQL Latest Row Per Group as the companion pattern.

Do Not Partition By the Thing You Are Ranking

For "top brands per category," the partition is category. The brand is what you rank inside that category.

This is usually wrong:

RANK() OVER (
  PARTITION BY category, brand
  ORDER BY total_sales DESC
)

If there is one row per brand, every row becomes rank 1 because each brand is in its own partition. The query is syntactically valid and logically useless for the prompt.

QUALIFY vs CTE

Some warehouses let you filter window-function results with QUALIFY:

SELECT
  category,
  brand,
  total_sales
FROM brand_sales
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY category
  ORDER BY total_sales DESC, brand ASC
) <= 2;

PostgreSQL and SQL Server do not support QUALIFY, so use a CTE or subquery. The logical order is the same: calculate the window value, then filter on it.

NULLs and Deterministic Ordering

If the ranking column can be missing, decide where NULL should sort. PostgreSQL supports explicit placement:

ORDER BY total_sales DESC NULLS LAST, brand ASC

For dialects without NULLS LAST, use a CASE expression:

ORDER BY
  CASE WHEN total_sales IS NULL THEN 1 ELSE 0 END,
  total_sales DESC,
  brand ASC

Also add a stable tie-breaker when using ROW_NUMBER. A primary key, timestamp, or deterministic text column is better than relying on storage order.

Dialect Notes

Which Function Should You Use?

Before You Submit the Query

• Did you aggregate to the correct grain before ranking?
• Did you partition by the group, not by the row being ranked?
• Did you choose the function based on tie behavior?
• Did you add a deterministic tie-breaker for ROW_NUMBER?
• Did you decide where NULL values belong?
• Did you verify whether the output should be exactly N rows or all rows in the top N ranks?

If the prompt involves running totals or rolling averages instead of top-N results, read SQL Window Frames Explained. Ranking and frames are both window-function topics, but they solve different problems.

FAQ

Can ROW_NUMBER return inconsistent results? Yes, if your ORDER BY does not uniquely order tied rows. Add a stable tie-breaker.

Should second-highest salary use RANK or DENSE_RANK? Usually DENSE_RANK, because the phrase usually means the second distinct salary value. Use RANK if the interviewer explicitly wants competition ranking.

Why does top 2 return 3 rows? Because you used a tie-preserving rank function and one of the ranks has a tie. That is correct if the requirement is top two ranks or top two distinct values.

Why not use LIMIT? LIMIT applies to the whole result set. Top-N per group needs a separate ranking inside each group.