ROW_NUMBER vs RANK vs DENSE_RANK

1. Why this trio trips people up

A window function computes a value across a set of rows related to the current row, without collapsing those rows the way GROUP BY does. ROW_NUMBER(), RANK(), and DENSE_RANK() all number rows within a group, and the only difference between them is how they handle ties — rows that share the same ORDER BY value. With no ties, all three return identical output, which is why the distinction so often goes unnoticed until it causes a bug.

The most common mistake is assuming the ORDER BY inside OVER() also sorts the final result set. It only controls the order rows are evaluated within each partition — the query still needs its own ORDER BY on the outside to come back sorted, as every query on this page does.

2. The mental model: PARTITION BY and ORDER BY

PARTITION BY splits the table into independent groups, the way GROUP BY does, but without collapsing each group into a single row — every row stays in the output, numbered relative to its own group. PARTITION BY department in the queries below means the numbering restarts at 1 for Engineering, restarts again for Marketing, and again for Sales, each ranked independently of the others.

ORDER BY inside OVER() decides the order rows are ranked in, not the order they're returned in. Ordering by salary DESC means the highest earner in each department gets rank 1; ORDER BY salary ASC would rank the lowest earner first instead.

3. The dataset

The table has 15 employees across three departments, with salaries chosen to produce three different tie shapes: Engineering has one two-person tie (Bob Martinez and Carol Nguyen, both 90000), Sales has a three-person tie (Frank Lee, Grace Kim, and Heidi Wagner, all 82000), and Marketing has two separate two-person ties (Judy Alvarez and Mallory Singh at 91000, Olivia Brooks and Peggy Torres at 84000). That range is what makes the difference between RANK() and DENSE_RANK() visible instead of theoretical — the three-way tie shows how far RANK()'s gap can stretch, and two ties inside one partition show whether DENSE_RANK() keeps closing the gap every time or only once.

Verified by actually running this against SQLite — not hand-computed.

CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (emp_id, name, department, salary) VALUES
  (1,  'Alice Chen',      'Engineering', 95000),
  (2,  'Bob Martinez',    'Engineering', 90000),
  (3,  'Carol Nguyen',    'Engineering', 90000),
  (4,  'Dave Okafor',     'Engineering', 88000),
  (5,  'Eve Patel',       'Engineering', 85000),
  (6,  'Frank Lee',       'Sales',       82000),
  (7,  'Grace Kim',       'Sales',       82000),
  (8,  'Heidi Wagner',    'Sales',       82000),
  (9,  'Ivan Petrov',     'Sales',       79000),
  (10, 'Judy Alvarez',    'Marketing',   91000),
  (11, 'Mallory Singh',   'Marketing',   91000),
  (12, 'Niaj Rahman',     'Marketing',   87000),
  (13, 'Olivia Brooks',   'Marketing',   84000),
  (14, 'Peggy Torres',    'Marketing',   84000),
  (15, 'Sybil Costa',     'Marketing',   80000);

4. ROW_NUMBER() — always unique, no exceptions

SELECT department, name, salary,
       ROW_NUMBER() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS row_num
FROM employees
ORDER BY department, salary DESC;

Notice Bob and Carol — tied at 90000 — still get different numbers (2 and 3). ROW_NUMBER() has no concept of a tie; it just counts rows in whatever order the database returns them when the ORDER BY values are equal.

That's the source of most ROW_NUMBER() bugs. When two rows tie, SQL doesn't guarantee which one gets the lower number — the assignment can differ between runs of the identical query, after an index rebuild, or after a query plan change, unless ORDER BY includes enough columns to make every row's position unique. Ranking Engineering by salary DESC alone picks arbitrarily between Bob and Carol for "row 2," and can pick differently another day, since salary alone doesn't break the tie. Adding a deterministic tiebreaker — employee id, hire date, name — fixes it.

ROW_NUMBER() is the right tool when uniqueness is the actual requirement: deduplicating rows that share a key, paginating a result set, or picking exactly one row per group.

5. RANK() — ties share a rank, then it skips

SELECT department, name, salary,
       RANK() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS rnk
FROM employees
ORDER BY department, salary DESC;

In Sales, three people tie for rank 1 — and the next rank is 4, not 2. RANK() counts the tied rows as occupying ranks 1, 2, and 3, even though it only prints "1" for each of them.

That gap breaks anything that assumes rank numbers are contiguous. A pagination query filtering rnk BETWEEN 1 AND 10 returns fewer than 10 rows whenever a tie straddles the boundary, since the gap consumes numbers without adding rows to fill it. A "top 3" query filtering rnk <= 3 against the Sales data returns all three tied rows — three rows for a "top 3" — because RANK() doesn't cap how many rows can share the top spot.

RANK() fits a genuine ranking or leaderboard, where a gap after a tie is the expected outcome: if three runners tie for first place, the next runner is fourth, not second.

6. DENSE_RANK() — ties share a rank, nothing skips

SELECT department, name, salary,
       DENSE_RANK() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS dense_rnk
FROM employees
ORDER BY department, salary DESC;

Same three-way tie in Sales — but the next rank is 2, not 4. Marketing shows this holding across two separate tie groups in one partition: Judy Alvarez and Mallory Singh tie for rank 1, Niaj Rahman is rank 2, and Olivia Brooks and Peggy Torres — tied at 84000 — are rank 3. Every tie compresses the ranks below it; ranks never skip.

The failure mode runs opposite to RANK()'s: a "top N" query written as dense_rnk <= 3 can return more than 3 rows whenever a tie lands inside that range, since DENSE_RANK() doesn't cap group size. Against the Marketing data, dense_rnk <= 3 returns six rows, not three, because two of those three rank positions each hold two people.

DENSE_RANK() is the right choice when what matters is the number of distinct values in the ranking, not the number of rows: price tiers, medal-style standings, or a "top N groups" query where a tie should count as one group, not extra output.

7. All three, side by side

SELECT department, name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY department, salary DESC;

Every row where row_num, rnk, and dense_rnk disagree marks a tie; every row where all three agree had nothing to break.

Marketing is the clearest case because it has two separate tie groups in one partition. Judy Alvarez and Mallory Singh tie at 91000 — ROW_NUMBER() splits them into 1 and 2, RANK() and DENSE_RANK() both give them 1. Niaj Rahman, untied at 87000, comes next: RANK() jumps to 3, skipping rank 2 entirely, while DENSE_RANK() simply steps to 2. Olivia Brooks and Peggy Torres then tie at 84000, both landing on RANK() 4 and DENSE_RANK() 3 — a normal step for both, since only a single untied row preceded them. But that tie sets up a second skip: Sybil Costa, the last row in the department at 80000, gets RANK() 6, skipping rank 5, while DENSE_RANK() moves cleanly to 4. Two ties in one partition cost RANK() two skipped numbers; DENSE_RANK() never skips, landing on four distinct values for six people.

8. Which one should you actually use?

ROW_NUMBER() fits anywhere the requirement is exactly one row per group, regardless of ties: deduplicating records that share a key, paginating a result set, or picking a single "most recent" or "highest" row per partition. Because it forces uniqueness even where the data is genuinely tied, it needs a fully deterministic ORDER BY, or the row it picks is arbitrary.

RANK() fits a genuine ranking or leaderboard, where ties should share a position and the next position should reflect how many rows are ahead of it — first, first, third, not first, first, second. The gap is meaningful there, not a bug.

DENSE_RANK() fits anywhere "rank" really means "which distinct group," independent of how many rows are in each group: pricing tiers, grading bands, or a "top N distinct values" query where ties inside the cutoff should all be included rather than treated as using up multiple slots.

9. What actually breaks in production

Non-deterministic ORDER BY is the most common source of bugs across all three functions, but it hits ROW_NUMBER() hardest, since ROW_NUMBER() is usually the one trusted to pick exactly one row. Ordering the Engineering data by salary DESC with no tiebreaker doesn't define which of Bob Martinez or Carol Nguyen gets row 1 — the database can pick either, and can pick differently after an index change or a query plan change, with identical data. A deduplication query that keeps "row_num = 1" per key can silently start keeping a different row after an unrelated change elsewhere in the system, with no error — just a report that quietly disagrees with yesterday's version of itself. The fix is always the same: extend ORDER BY with enough columns — id, timestamp, name — that no two rows can tie.

RANK()'s gaps break pagination logic that assumes rank numbers are contiguous. A query fetching "ranks 1 through 10" comes back short whenever a tie falls inside that window, because the gap after a tie consumes rank numbers without producing rows. That bug tends to surface as a row count that's only wrong with certain data, easy to miss against a tie-free test sample and easy to hit once real data has ties.

DENSE_RANK()'s failure runs the other way: a filter like dense_rnk <= N can return more than N rows whenever a tie sits at the boundary. That's often correct — but only if the intent was "top N distinct groups," not "top N rows." Mixing up the two produces a query that runs without error and returns a row count nobody explicitly asked for.

10. Cheat sheet

The differences come down to two questions: do ties share a number, and does the number after a tie skip. ROW_NUMBER() never shares and never skips, because there's nothing to skip. RANK() shares and skips by the tie size. DENSE_RANK() shares and never skips.

11. Try it yourself

Without ties, ROW_NUMBER(), RANK(), and DENSE_RANK() are indistinguishable — so the choice only matters the moment two rows share an ORDER BY value, and that's exactly the moment most people haven't thought about which one they actually need.

Practice window functions live on TableNotFound →