Ian Klosowicz
RANK, DENSE_RANK, and ROW_NUMBER all number rows inside an ordered group, and they only differ in how they treat ties. ROW_NUMBER gives every row a unique number with no ties, even when two rows have the same value. RANK gives tied rows the same number, then skips the next numbers to leave a gap. DENSE_RANK gives tied rows the same number but doesn't skip, so the next number follows with no gap. Pick ROW_NUMBER when you need one row per group, RANK when gaps after ties are correct, and DENSE_RANK when you want ranks with no gaps.
That's the whole difference. The rest of this post walks through a single example so you can see all 3 side by side, then covers which one to use for the jobs that actually come up, how ties and ORDER BY decide the result, and the mistakes that produce the wrong winner.
All 3 are window functions that assign a number to each row based on an ORDER BY inside a window. They share the same syntax and the same ordering. The only thing that separates them is what happens when two rows tie on the ordering value.
Here is the short version:
If there are no ties in your ordering, all 3 return the exact same numbers. The differences only ever appear when values repeat, which is why ties are the whole story here.
The fastest way to lock this in is one small example. Say you have 5 players and their scores, and you order by score from high to low. The scores are 95, 95, 88, 70, and 70.
Ordering by score descending, here is the number each function assigns, row by row from the top:
| Score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 95 | 1 | 1 | 1 |
| 95 | 2 | 1 | 1 |
| 88 | 3 | 3 | 2 |
| 70 | 4 | 4 | 3 |
| 70 | 5 | 4 | 3 |
Read down each column and the behavior is obvious. ROW_NUMBER counts 1 through 5 with no repeats. RANK repeats on the ties and then jumps, so it goes 1, 1, 3, 4, 4, and there is no 2. DENSE_RANK repeats on the ties but never jumps, so it goes 1, 1, 2, 3, 3. Same data, same ORDER BY, three different numbering rules.
Use ROW_NUMBER when you need exactly one row per group, or a clean sequential index with no duplicates. It's the one you want for deduplication and for most "latest record" problems.
The jobs ROW_NUMBER is built for:
The catch is that ROW_NUMBER breaks ties arbitrarily. If two rows have the same timestamp, it still picks one as number 1, and which one can change between runs unless your ORDER BY has a tiebreaker. Add a second ordering column, like an ID, so the choice is stable. I run into this constantly in Snowflake when I dedupe staged data, and a tiebreaker in the ORDER BY is what makes the result repeatable instead of a coin flip.
Use RANK when ties should share a position and the gap after them is meaningful. This is the behavior people expect from competition standings.
Think about a race. If two runners tie for first, they are both first, and the next runner is third, not second. Nobody finished second, so the gap is correct. RANK encodes that: the skipped number tells you how many competitors were ahead.
RANK fits when:
That last point is the main reason to reach for RANK over ROW_NUMBER. If you filter ROW_NUMBER = 1 you get a single winner even when there is a tie. Filter RANK = 1 and you get every tied top row, which is usually what "who is in first" actually means.
Use DENSE_RANK when you want ranks with no gaps, usually because the rank number is going to be read as a level or tier rather than a finishing position.
DENSE_RANK fits when:
The "top N distinct values" case is where DENSE_RANK earns its spot. If you want the 3 highest salaries in each department and several people share a salary, DENSE_RANK <= 3 returns all of them across exactly 3 pay levels. RANK would skip levels after ties, and ROW_NUMBER would cut people off mid-tier.
Every one of these functions is driven by the ORDER BY inside the OVER clause, and optionally a PARTITION BY. Get those two right and the function almost picks itself.
ORDER BY defines the ranking. The numbers follow the sort you give. Order by score descending and the highest score is number 1. Change the direction and you flip the whole thing. There is no default, so the ORDER BY is doing all the work of deciding what "first" means.
PARTITION BY restarts the count per group. Add PARTITION BY department and the numbering resets to 1 at the start of each department. Without it, the function ranks the entire result as one set. Most real ranking questions, like top 3 per category, need a PARTITION BY.
Ties are decided by whatever you put after the first sort key. If your ORDER BY only lists score, rows with the same score are tied, and ROW_NUMBER breaks that tie unpredictably. Add a stable second column to the ORDER BY and the tie resolves the same way every time. RANK and DENSE_RANK still treat equal first-key values as ties, which is the point of using them.
Most ranking bugs are not errors. The query runs and returns a plausible result that's quietly wrong. These are the ones I see most from the people who message me about their SQL:
The habit that prevents most of these: decide first whether you want rows or positions, and whether ties should be visible. That single choice points you at the right function before you write a line.
What is the main difference between RANK and DENSE_RANK?
Both give tied rows the same number. The difference is what comes after a tie. RANK skips numbers, so two rows tied for 1 are followed by 3. DENSE_RANK doesn't skip, so the same tie is followed by 2. Use RANK when the gap is meaningful, DENSE_RANK when you want ranks with no gaps.
When should I use ROW_NUMBER instead of RANK?
Use ROW_NUMBER when you need exactly one row per group with no ties, like keeping the latest record per customer or deduplicating. ROW_NUMBER forces a unique number on every row. Use RANK when tied rows should genuinely share a position, like a leaderboard where two entries can both be first.
Do these functions need an ORDER BY?
Yes. All 3 rank rows based on the ORDER BY inside the OVER clause, and without it the numbering has no meaning. You usually also add PARTITION BY to restart the count for each group, like each department or category. ORDER BY sets the ranking direction, PARTITION BY sets the groups.
How do I get the top 3 per group?
Add PARTITION BY the group and ORDER BY the value, wrap the query, and filter on the function. For exactly 3 rows per group use ROW_NUMBER <= 3. For the 3 highest distinct values, including ties, use DENSE_RANK <= 3. The one you pick depends on how you want ties handled.
Are RANK, DENSE_RANK, and ROW_NUMBER the same if there are no ties?
Yes. When every value in the ordering is distinct, all 3 return identical numbers, 1 through N. The differences only appear when two or more rows share the same ordering value. That's why ties are the only thing you need to think about when choosing between them.
Do these work the same across databases?
Mostly yes. RANK, DENSE_RANK, and ROW_NUMBER are standard SQL window functions and behave the same in Snowflake, BigQuery, Postgres, SQL Server, and others. The syntax of the OVER clause is consistent too. The behavior you learned in one of them carries over to the rest.
These 3 functions come down to one decision: do you want unique row numbers, positions with gaps after ties, or positions without gaps. Once you frame it that way, picking the right one takes a second. The harder skill is reaching for a window function at all under interview pressure, when a "top N per group" question lands and the clock is running. Building that reflex on real tables is what Analyst Hive is built for: a 90-day daily-task program for aspiring data analysts who want SQL that holds up when someone is watching. You can see the approach at Analyst Hive.
