RANK vs DENSE_RANK vs ROW_NUMBER: How Each Handles Ties

RANK vs DENSE_RANK vs ROW_NUMBER in one line: all three number rows by the same ORDER BY, and they only differ when two rows tie. RANK() gives tied rows the same number and then skips, so the next number is where it would have been with no tie. DENSE_RANK() gives tied rows the same number and does not skip. ROW_NUMBER() never repeats a number, so tied rows get different numbers in an order the engine chooses, unless your ORDER BY breaks the tie. The difference matters most in the "top N per group" query, where the three functions return different numbers of rows. Every query below is run live in SQLite.

Function	Tied rows	Numbers for 70, 55, 55, 40
RANK()	same number, then a gap	1, 2, 2, 4
DENSE_RANK()	same number, no gap	1, 2, 2, 3
ROW_NUMBER()	different numbers, arbitrary order	1, 2, 3, 4

The problem it solves

"Which were our three busiest days?" sounds like ORDER BY loaves DESC LIMIT 3, and it is, until two days tie for third. Then a LIMIT picks one of them for no reason, and the report changes between runs. "Top three per shop" cannot use LIMIT at all, because LIMIT cuts the whole result, not each group. The ranking functions solve both: they attach a position to every row, and you decide what to do with ties when you filter on that position.

What OVER, PARTITION BY and frames do in general is in SQL window functions explained. This article is only about the three numbering functions and the one case where they disagree.

The intuition

Picture a race. Three runners finish; two of them cross the line at exactly the same time, behind the winner.

• RANK() is the podium. The two share second place, and the next runner is fourth, because three people finished ahead of them. The number is "one plus how many rows are strictly ahead of me", so ties leave gaps.
• DENSE_RANK() is the list of distinct results. The two share second, and the next runner is third. The number is "how many distinct values are ahead of me, plus one", so there are no gaps.
• ROW_NUMBER() is the finish-line photo. Someone has to be listed first. The two tied runners get 2 and 3, and which gets which is decided by whatever order the engine happened to read them in. Add a tie-breaker to the ORDER BY, and the choice becomes yours.

All three restart at 1 in every PARTITION BY group, and all three are computed after WHERE, so you cannot filter on them in the same query level. That is why "top N per group" always has a subquery or a CTE around it.

Two cousins, in one line each: NTILE(k) deals the ordered rows into k buckets of as-equal-as-possible size, and PERCENT_RANK() is (RANK() - 1) / (rows - 1), a rank scaled to 0 through 1.

Watch it run

The animation uses the lesson's four days of baking. GROUP BY produces the total but folds the four rows into one. OVER (ORDER BY day) does the same addition row by row: the first row sees only itself, the second sees two rows, and the last row's running total equals the GROUP BY answer. A second window then ranks the same rows by loaves, in the same query. That second window is RANK(); with no tie in the lesson's data, all three functions would print the same numbers there. The code below adds the tie.

The code

The lesson's bake table, now with a shop column and a second week. Mill Lane's two 55-loaf days are the tie:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE bake (shop TEXT, day TEXT, loaves INT);
  INSERT INTO bake VALUES
    ('Mill Lane', '03-04', 40), ('Mill Lane', '03-05', 55),
    ('Mill Lane', '03-06', 55), ('Mill Lane', '03-07', 70),
    ('Harbour',   '03-04', 62), ('Harbour',   '03-05', 48),
    ('Harbour',   '03-06', 62), ('Harbour',   '03-07', 62);
""")
for row in db.execute("""
  SELECT day, loaves,
         RANK()       OVER (ORDER BY loaves DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY loaves DESC) AS dense,
         ROW_NUMBER() OVER (ORDER BY loaves DESC) AS rn
  FROM bake WHERE shop = 'Mill Lane'
  ORDER BY loaves DESC, day"""):
    print(row)
# ('03-07', 70, 1, 1, 1)
# ('03-05', 55, 2, 2, 2)
# ('03-06', 55, 2, 2, 3)
# ('03-04', 40, 4, 3, 4)

The 40-loaf day is the whole story: rank 4, dense rank 3, row number 4. Two rows were strictly ahead of it in RANK(), so the gap; two distinct values were ahead of it in DENSE_RANK(), so no gap.

Now "top two days per shop", once with each function. Harbour has a three-way tie for first, so the three answers differ in size:

for fn in ["RANK()", "DENSE_RANK()", "ROW_NUMBER()"]:
    rows = db.execute(f"""
      WITH numbered AS (
        SELECT shop, day, loaves,
               {fn} OVER (PARTITION BY shop ORDER BY loaves DESC) AS n
        FROM bake)
      SELECT shop, day, loaves FROM numbered
      WHERE n <= 2 ORDER BY shop, loaves DESC, day""").fetchall()
    print(f"{fn:13} {len(rows)} rows:", rows)
# RANK()        6 rows: [('Harbour', '03-04', 62), ('Harbour', '03-06', 62), ('Harbour', '03-07', 62), ('Mill Lane', '03-07', 70), ('Mill Lane', '03-05', 55), ('Mill Lane', '03-06', 55)]
# DENSE_RANK()  7 rows: [('Harbour', '03-04', 62), ('Harbour', '03-06', 62), ('Harbour', '03-07', 62), ('Harbour', '03-05', 48), ('Mill Lane', '03-07', 70), ('Mill Lane', '03-05', 55), ('Mill Lane', '03-06', 55)]
# ROW_NUMBER()  4 rows: [('Harbour', '03-04', 62), ('Harbour', '03-06', 62), ('Mill Lane', '03-07', 70), ('Mill Lane', '03-05', 55)]

Read the three counts as three different questions. RANK() <= 2 means "every day that was first or second", and Harbour's three tied days are all first, so all three come through and nothing is second. DENSE_RANK() <= 2 means "the two best distinct totals", so Harbour's 48 joins the three 62s. ROW_NUMBER() <= 2 means "exactly two rows per shop", and for Harbour it picked 03-04 and 03-06 out of three equal days, with 03-07 left out for no reason the data gives.

The fix for that last arbitrariness is a tie-breaker, and the WHERE restriction is enforced by the engine:

print(db.execute("""
  SELECT day, loaves,
         ROW_NUMBER() OVER (ORDER BY loaves DESC, day DESC) AS rn
  FROM bake WHERE shop = 'Harbour' ORDER BY rn""").fetchall())
# [('03-07', 62, 1), ('03-06', 62, 2), ('03-04', 62, 3), ('03-05', 48, 4)]

try:
    db.execute("SELECT day FROM bake WHERE RANK() OVER (ORDER BY loaves DESC) = 1")
except sqlite3.OperationalError as e:
    print(e)
# misuse of window function RANK()

print(db.execute("""
  SELECT day, loaves,
         NTILE(2) OVER (ORDER BY loaves DESC) AS half,
         ROUND(PERCENT_RANK() OVER (ORDER BY loaves DESC), 2) AS pct
  FROM bake WHERE shop = 'Mill Lane' ORDER BY loaves DESC, day""").fetchall())
# [('03-07', 70, 1, 0.0), ('03-05', 55, 1, 0.33), ('03-06', 55, 2, 0.33), ('03-04', 40, 2, 1.0)]

With day DESC as the second key, the most recent of the tied days is always row 1, on every run and every engine. NTILE(2) splits the four rows into two halves, and it has to split the tied 55s to do it; PERCENT_RANK() gives both 55s the same 0.33, because it is built on RANK().

The complexity

All three cost the same: the engine sorts each partition by the ORDER BY, O(n log n) per partition, then walks it once assigning numbers in O(n). An index whose order matches PARTITION BY then ORDER BY lets the engine skip the sort. The filter on the number happens afterwards, in the outer query, so "top 3 per group" still numbers every row of every group before it throws most of them away.

Where it goes wrong

• ROW_NUMBER() with ties and no tie-breaker. The "top one per group" row changes between runs or after an index is added. Add a column that is unique within the partition to the ORDER BY.
• RANK() <= N with many ties. You asked for three rows and got thirty, because they all tied for first. If exactly N rows is the requirement, use ROW_NUMBER(); if "everyone who placed" is the requirement, RANK() is correct and the thirty rows are the answer.
• Expecting dense ranks to be positions. A dense rank of 3 does not mean two rows are ahead of you; it means two distinct values are. Use RANK() for "how many beat me".
• Putting the function in WHERE. Window functions run after WHERE, so the engine rejects it. Wrap the query in a CTE or subquery and filter on the alias.
• Forgetting PARTITION BY. Without it, the numbering runs over the whole table, and "top 3 per shop" becomes "top 3 overall, labelled by shop".

When it shows up in interviews

The classic prompt is "the second highest salary in each department" or "the top 3 products per category", and the interviewer is waiting to hear what happens on a tie. A complete answer names the function, explains why the filter needs an outer query, and says what the result would look like if two rows tied. The follow-up is often the reverse: "there is a gap in these ranks, why?" The running-total and frame side of the same syntax is covered in SQL window functions explained, and the ordered pagination problem that ROW_NUMBER() feeds is in ORDER BY and LIMIT: offset vs keyset pagination.

How to say it in an interview

"All three number rows by the ORDER BY inside OVER, restarting in each partition. They differ only on ties. RANK() gives tied rows the same number and then skips ahead, so you get gaps; DENSE_RANK() gives them the same number without the gap; ROW_NUMBER() always gives distinct numbers, so on a tie the order is arbitrary unless I add a tie-breaker. For 'exactly N per group' I use ROW_NUMBER() with a deterministic ORDER BY; for 'everyone who placed in the top N' I use RANK(); for 'the N best distinct values' I use DENSE_RANK(). The filter goes in an outer query or a CTE, because window functions are computed after WHERE."

The table and the first ranking query come from the Window Functions lesson.

Frequently asked questions

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same number. RANK() then skips: after two rows tied at 2, the next row is 4, because three rows are ahead of it. DENSE_RANK() does not skip: the next row is 3, because two distinct values are ahead of it. With no ties they are identical.

When should I use ROW_NUMBER instead of RANK?

Use ROW_NUMBER() when you need exactly one number per row, for example to keep exactly N rows per group, to pick one row out of duplicates, or to paginate. Make the ORDER BY deterministic with a unique tie-breaker, otherwise tied rows are numbered in an order the engine chooses.

Why can't I use RANK in a WHERE clause?

Window functions are evaluated after WHERE, GROUP BY and HAVING, on the rows that survived them, so the engine has no value to test yet. Compute the rank in a subquery or CTE with an alias, then filter on that alias in the outer query.

Sources

• Window Functions — SQLite Documentation
• Window Functions — PostgreSQL Documentation
• Window Function Descriptions — MySQL 8.4 Reference Manual