ROW_NUMBER, RANK, DENSE_RANK Compared

1. Three ranking functions, one purpose: establishing order

Anyone who can cleanly tell these three functions apart has permanently removed one of the most common stumbling blocks in everyday practical SQL, because ranking functions show up sooner or later in nearly every more complex reporting query.

ROW_NUMBER, RANK and DENSE_RANK belong to the so-called ranking functions, a subgroup of window functions that assign a position number to every row within an order. All three require an ORDER BY inside the OVER clause, because without a defined order no rank can be computed. At first glance they produce the same numbers in many examples, which creates the false impression that they are interchangeable.

The difference only shows up once two or more rows share the same sort value, that is, when a tie occurs. This exact case decides which of the three ranking functions is right for the use case at hand. Anyone who needs a unique, gapless row number, for example for deduplication or pagination, reaches for ROW_NUMBER. Anyone who wants to represent true ranks with sports-style logic, where two first-place finishers cause the third place to be skipped, needs RANK or DENSE_RANK, depending on whether gaps are desired.

These three ranking functions have been a fixed part of the SQL standard since SQL:2003 and are available in practically every modern relational database, from PostgreSQL to MySQL from version 8.0 to SQL Server and Oracle. The syntax is nearly identical across systems, which makes them one of the most portable and, at the same time, one of the most commonly misunderstood tools in SQL.

2. ROW_NUMBER: unique, gapless numbering

ROW_NUMBER() OVER (ORDER BY column) assigns each row a unique, sequential integer starting at 1, completely independent of whether several rows share the same sort value. On a tie, the database decides internally which of the equal rows comes first, which can lead to non-deterministic results without a unique tie-breaker column in the ORDER BY. That is the most important practical note about ROW_NUMBER: without a unique column in the ORDER BY, for instance a primary key column as a second sort criterion, the same query can produce different numbering across two executions.

ROW_NUMBER always guarantees a gapless sequence from 1 to the number of rows in the partition. There are never two rows with the same number, even if their sort values are identical. This property makes ROW_NUMBER the tool of choice for any task where a unique identifier per row is needed, independent of the business rank: paginating result sets, picking exactly one row per group, or identifying duplicates.

-- ROW_NUMBER: always unique, always gapless, per row
SELECT
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num
FROM product_sales;

-- Result
-- product_name | revenue | row_num
-- Widget A     |   50000 |       1
-- Widget B     |   50000 |       2   -- tie, but still gets a distinct number
-- Widget C     |   42000 |       3
-- Widget D     |   30000 |       4

3. RANK: ranks with gaps on ties

RANK() OVER (ORDER BY column) follows the logic of a classic sports leaderboard: rows with an identical sort value receive the same rank, and the next different value jumps directly to the rank corresponding to the number of rows already assigned. Two first-place finishers therefore both get rank 1, and the next value gets rank 3, not rank 2. This gap is not a bug but intentional behavior, and it matches exactly the logic known from competitions and leaderboards.

The reason for this gap lies in the definition of RANK: the rank of a row always equals 1 plus the number of rows that precede it in the sort order. With two tied first-place rows, the third row already has two rows before it, so it gets rank 3. This behavior is particularly important in use cases where the actual position within the overall set matters, such as percentile calculations or when prize money is distributed according to exact placement.

4. DENSE_RANK: ranks without gaps

DENSE_RANK() OVER (ORDER BY column) solves the same problem as RANK, but avoids the gaps. Rows with an identical sort value receive the same rank as with RANK, but the next different value gets the immediately following rank, without skipping any numbers. With two first-place rows, the third row gets rank 2, not rank 3. DENSE_RANK effectively counts the number of distinct values encountered up to the current row, rather than the number of rows.

This property makes DENSE_RANK ideal for use cases where you care about the number of distinct performance tiers, not the absolute position. A typical example is classifying products into price tiers: if three products share the same price, they should belong to the same tier, and the next tier should differ from the previous one by exactly one, regardless of how many products were in the previous tier.

5. The decisive difference in one example

The difference between the three ranking functions is best demonstrated on a single dataset with a tie, where all three functions are computed side by side. This exact comparison shows why choosing the right ranking function is not a stylistic decision, but one that materially changes the content of the query result.

-- All three ranking functions side by side, same tie
SELECT
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM exam_results;

-- Result
-- student_name | score | row_num | rank_val | dense_rank_val
-- Julia Fischer|    95 |       1 |        1 |              1
-- Marco Bauer  |    95 |       2 |        1 |              1
-- Nina Roth    |    88 |       3 |        3 |              2   -- RANK skips 2, DENSE_RANK does not
-- Felix Hahn   |    75 |       4 |        4 |              3

Julia and Marco are tied with the same score. ROW_NUMBER still assigns two different numbers, 1 and 2, with no real meaning for the order between the two. RANK assigns rank 1 to both and jumps directly to rank 3 for Nina, because two rows already precede her. DENSE_RANK also assigns rank 1 to both, but continues with rank 2 for Nina, because it only counts distinct values. Same underlying data, three business-wise different results.

6. Deduplicating records with ROW_NUMBER

One of the most common practical applications of ROW_NUMBER is deduplicating records directly in SQL, without application logic. The pattern: ROW_NUMBER() OVER (PARTITION BY column-that-should-be-unique ORDER BY timestamp DESC) assigns a sequential number within every group of duplicates, with the newest row getting number 1 thanks to ORDER BY DESC. Every row with a number greater than 1 is, by definition, a duplicate and can be deleted or excluded from further analysis.

RANK or DENSE_RANK would be unsuitable for this purpose, because on an exact tie in the ORDER BY, both duplicates would receive the same rank 1 and thus both be marked as "keep", instead of exactly one row being selected. ROW_NUMBER, on the other hand, always guarantees exactly one row with number 1 per partition, which makes it the only correct choice for deduplication, top-1-per-group selection, and similar tasks where exactly one row per logical group is required.

-- Deduplicate: keep only the newest row per email address
DELETE FROM contacts
WHERE contact_id IN (
    SELECT contact_id FROM (
        SELECT
            contact_id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY created_at DESC
            ) AS rn
        FROM contacts
    ) ranked
    WHERE rn > 1
);

7. Leaderboard scenarios with RANK and DENSE_RANK

For leaderboards, top lists and competition results, RANK and DENSE_RANK are the natural choice, because they correctly represent the business expectation of a leaderboard. In a sales leaderboard where two salespeople have achieved the same revenue, business stakeholders typically expect both to hold the same place, not arbitrarily different places as with ROW_NUMBER. Whether RANK or DENSE_RANK is correct depends on whether the number of participants below a given rank matters for the business.

For a classic prize-money scenario, where the third-place participant gets less if two participants share first place, RANK is correct because it reflects the actual number of participants ahead of a given one. For a classification into performance tiers, where only the number of distinct tiers matters, DENSE_RANK is the better choice, because consecutive tiers always differ by exactly one, regardless of the group size of the previous tier.

-- Leaderboard with RANK: tied top scores share rank 1
SELECT
    salesperson,
    total_sales,
    RANK() OVER (ORDER BY total_sales DESC) AS leaderboard_rank
FROM quarterly_sales
ORDER BY total_sales DESC;

8. Combining with PARTITION BY for grouped rankings

All three ranking functions reach their full practical potential only in combination with PARTITION BY, which computes the rank within individual groups rather than across the entire result set. A typical request is: "The three highest-revenue products per category." Without PARTITION BY, RANK or ROW_NUMBER would number the products across all categories, which could cause smaller categories to be entirely absent from the result. With PARTITION BY category, numbering restarts at 1 for every category.

This pattern of PARTITION BY plus a ranking function plus an outer WHERE condition on the rank value is one of the most common query shapes in reporting systems altogether. Since ranking functions cannot be filtered directly in the WHERE clause, the rank computation is wrapped in a subquery or common table expression, and filtering on, for example, "rank less than or equal to 3" happens in the outermost SELECT. This two-step pattern is standard and should be firmly internalized for recurring top-N-per-group analyses.

Another practical aspect of working with the three ranking functions is combining them with downstream filtering. Since all three functions are only evaluated after WHERE, GROUP BY and HAVING, their result cannot be filtered directly in the same query. Anyone who, for example, only wants to see rows with RANK() equal to 1 has to wrap the rank computation in a subquery or common table expression and perform the filtering in the outermost SELECT, exactly as with other window functions.

-- Top 3 products per category, using RANK inside a subquery
SELECT category, product_name, revenue, category_rank
FROM (
    SELECT
        category,
        product_name,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
    FROM product_revenue
) ranked
WHERE category_rank <= 3
ORDER BY category, category_rank;

9. Performance and index usage for ranking functions

All three ranking functions internally require sorting the data by the PARTITION BY and ORDER BY columns before the rank values can be computed. Without a matching index, the database executes this sort explicitly as its own step in the execution plan, which noticeably costs time on large tables. A composite index that covers the PARTITION BY columns first and the ORDER BY columns second often lets the optimizer use the already sorted index entries directly and avoid the separate sort step.

In practice, ROW_NUMBER, RANK and DENSE_RANK barely differ in raw computation speed, since they operate on the same sorted row sequence and only the number assignment logic differs. The real performance lever almost always lies in the presence of a matching index and in whether the top-N-per-group pattern is combined with an outer filter on the rank value. Without that filter, all rows including their rank values have to be materialized, even if in the end only the top three of each group matter.

One last practically relevant note concerns combining the three ranking functions with NTILE, a related window-only function that divides the result set into a fixed number of equally sized groups. While ROW_NUMBER, RANK and DENSE_RANK assign a rank to every row, NTILE(4) divides the sorted rows into four quartiles, which is excellent for percentile analyses where the interest is not the exact rank, but membership in one of several equally sized segments.

10. Summary

This clarity about tie behavior is ultimately the decisive edge in knowledge that separates a correct ranking calculation from a flawed one.

ROW_NUMBER, RANK and DENSE_RANK solve the same fundamental problem of assigning a position number to rows, but behave fundamentally differently on ties. ROW_NUMBER always assigns unique, gapless numbers, regardless of duplicates in the sort value, which makes it the right choice for deduplication and top-1-per-group selection. RANK assigns the same rank on a tie and then leaves a gap, matching the classic sports leaderboard logic. DENSE_RANK also assigns the same rank on a tie, but skips the gap and effectively counts distinct values instead of rows.

The choice between the three ranking functions is not a matter of taste, it depends directly on the business use case. Anyone who picks the wrong ranking function, for instance RANK instead of ROW_NUMBER for deduplication, risks multiple duplicates being marked as rank 1 at once and therefore incorrectly kept. A conscious look at the desired tie behavior before writing the query saves a lot of debugging effort afterward.

11. FAQ: ROW_NUMBER, RANK and DENSE_RANK