ROW_NUMBER versus RANK versus DENSE_RANK on tied sales

ROW_NUMBER assigns a unique sequence position to each row. RANK gives equal ordering values the same rank and leaves gaps after ties. DENSE_RANK also shares ranks across ties but does not leave gaps. The right function depends on what the business means by “top.”

“Show exactly three representatives” is different from “show everyone in the top three sales groups.” A dashboard can satisfy one while violating the other, even when the ranking syntax is correct.

The following synthetic example runs in the advanced SQL lab. It is not an assessment of real employees or a recommended compensation policy.

Make the tie visible

WITH sales(rep_id, sales_paise) AS (
    VALUES ('A',120000), ('B',120000), ('C',90000), ('D',80000)
)
SELECT rep_id, sales_paise,
       ROW_NUMBER() OVER (ORDER BY sales_paise DESC, rep_id) AS row_num,
       RANK() OVER (ORDER BY sales_paise DESC) AS sales_rank,
       DENSE_RANK() OVER (ORDER BY sales_paise DESC) AS dense_sales_rank
FROM sales
ORDER BY sales_paise DESC, rep_id;

Expected output:

RANK behaves like competition positions: two people share first, so the next is third. DENSE_RANK numbers the distinct score groups: the third row belongs to the second distinct score.

ROW_NUMBER needs a stable secondary key if tied rows must be selected reproducibly. Here rep_id establishes an arbitrary but explicit display/selection order. It is not a claim that A performed better than B.

Do not accidentally break the tie definition

The ranking functions use only sales in their ORDER BY, while ROW_NUMBER uses sales and representative ID. If you add the unique ID to RANK's ordering, A and B no longer have identical ordering values and will receive different ranks.

That is a common error: adding a tie-breaker intended for stable display also changes the definition of equal performance. Keep the business ranking order distinct from the final display order.

Window ordering does not guarantee the final result's display order. Use the outer ORDER BY for that. The SQLite window-function reference documents both ranking behaviour and the separation of window and result ordering.

Translate the top-N request into a rule

For exactly three rows, ROW_NUMBER gives A, B and C. For the top three distinct groups, DENSE_RANK includes all four representatives. Neither answer is inherently correct without the request's tie policy.

Compute the rank in a CTE or derived table, then filter it in an outer query:

WITH sales(rep_id, sales_paise) AS (
    VALUES ('A',120000), ('B',120000), ('C',90000), ('D',80000)
), ranked AS (
    SELECT rep_id, sales_paise,
           DENSE_RANK() OVER (ORDER BY sales_paise DESC) AS position
    FROM sales
)
SELECT rep_id, sales_paise, position
FROM ranked
WHERE position <= 2
ORDER BY position, rep_id;

This returns A, B and C. It avoids trying to filter a window result in the same SELECT's WHERE clause, which is not the appropriate evaluation stage in SQLite.

Partition only when rankings should restart

PARTITION BY region produces separate rankings within each region. Without it, all representatives are ranked together. A regional first place and an overall first place are different labels.

Before ranking, aggregate the source to the intended entity and reporting period. Ranking transaction rows when the request concerns monthly representative totals ranks purchases, not people. Check that the input has one row per representative-period after aggregation.

If the data contains missing sales values, define how they are handled. Null ordering differs across engines and can be controlled explicitly. Excluding incomplete records or labelling them separately may be clearer than assigning a performance rank to unknown values.

Validate more than the first row

Test an exact tie, a near tie, a missing value and a group with fewer than N members. Confirm that the output row count matches the chosen rule. If the result feeds a consequential decision, make the tie policy visible to the decision owner.

Exercise: change B's sales to 119,999. Predict all three rankings, then restore the tie and add a fifth representative at 90,000. Explain how each top-two rule behaves. The exercise tests whether you understand the ordering contract rather than remember a memorized ranking table.

NeuraPath's Data Analytics with Generative AI programme covers advanced SQL for business reporting. Ranking is useful when its definition is explicit; a generated “top performers” query still needs a human to decide what ties, populations and periods mean.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with SQL running totals: choose the correct window frame.
• Then apply it in LAG and LEAD for repeat-purchase intervals.