← All Kits · SQL Kit

RANK vs DENSE_RANK vs ROW_NUMBER in SQL

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

By the end of this page you will pick the right ranking function in about five seconds, every time. The three functions do the same job and differ on exactly one thing: what they do when two rows tie. That single difference decides how many rows your "top three" returns, and on the ten-row table below the answer is 6, 8 or 10 rows depending on which one you chose.

What you do is choose by the question. "Give me three rows" is ROW_NUMBER. "Give me everyone in the top three positions" is RANK. "Give me everyone at the top three scores" is DENSE_RANK. The rest of this page shows each one running, so you can watch the row count change.

The short version. All three number rows in order. They only disagree about ties.

The table the examples run on

Ten sales reps, two regions, one column of closed deals each. It is small on purpose, so you can check every number on this page by hand.

CREATE TABLE sales(rep TEXT, region TEXT, deals INT, signed_on TEXT);

INSERT INTO sales VALUES
('Alvarez','North',12,'2026-07-02'),
('Brennan','North',12,'2026-07-05'),
('Cho',    'North', 9,'2026-07-01'),
('Diaz',   'North', 9,'2026-07-09'),
('Ellis',  'North', 7,'2026-07-11'),
('Fowler', 'South',15,'2026-07-03'),
('Grant',  'South',11,'2026-07-06'),
('Hale',   'South',11,'2026-07-08'),
('Iyer',   'South',11,'2026-07-12'),
('Jansen', 'South', 4,'2026-07-14');

North has two ties. Alvarez and Brennan both closed 12, and Cho and Diaz both closed 9. South has a three way tie at 11 deals. Those ties are the whole subject of this page.

All three, side by side, on the same rows

Before you read the output: Alvarez and Brennan both closed 12. What number do you expect each function to give Cho, who closed 9?

SELECT rep, deals,
       ROW_NUMBER() OVER (ORDER BY deals DESC) AS rn,
       RANK()       OVER (ORDER BY deals DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY deals DESC) AS dr
FROM sales
WHERE region = 'North';

Read down each column and the rule states itself.

ROW_NUMBER hands out 1 to 5 and never repeats. It has to give Alvarez and Brennan different numbers, so it picks one of them to be second, and it picks arbitrarily. Run the same query tomorrow and Brennan can be first.

RANK gives both 12s a 1, then jumps to 3 for Cho. The 2 is gone because the tie already used that place up. Cho really is the third rep down the list.

DENSE_RANK gives both 12s a 1, then gives Cho a 2. It is counting distinct deal counts, not rows. Three counts exist in North, so its numbers stop at 3.

Say this one out loud before reading on: why can RANK skip a number while DENSE_RANK never can? The answer is in what each one counts. RANK counts places in the list. DENSE_RANK counts distinct values.

Use ROW_NUMBER when you want exactly one row per group

First: you want the single best rep in each region. Which of the three can you use safely?

Only ROW_NUMBER, because it is the only one that promises a single row numbered 1. Number the rows inside each region, then keep the ones numbered 1.

SELECT rep, region, deals
FROM (
  SELECT rep, region, deals,
         ROW_NUMBER() OVER (PARTITION BY region
                            ORDER BY deals DESC, signed_on) AS rn
  FROM sales
) numbered
WHERE rn = 1;

Two things are doing work there. PARTITION BY region restarts the numbering for each region, so both regions get their own 1. And signed_on is a tiebreaker. Alvarez and Brennan both closed 12, so the earlier signing date decides it.

Without that second sort column the winner is whichever row the engine happened to produce first, and it can change between runs. So: always give ROW_NUMBER a tiebreaker. Any column that cannot repeat will do, and a primary key is the safest one. This is the most common defect in real deduplication queries. They run, they return the right number of rows, and they quietly pick a different row each time.

Same shape, different job. This is also how you keep one row out of each set of duplicates. Partition by whatever makes a row a duplicate, order by the version you want to keep, and take number 1. Finding those sets in the first place is covered in how to find duplicate rows.

Use RANK when tied rows deserve the same place

Before the query: three reps in South are tied at 11 deals. Should the leaderboard call one of them fourth?

No, and that is what RANK is for. It is the medal rule. Two golds means no silver.

SELECT rep, region, deals
FROM (
  SELECT rep, region, deals,
         RANK() OVER (PARTITION BY region ORDER BY deals DESC) AS rk
  FROM sales
) ranked
WHERE rk <= 3
ORDER BY region, deals DESC;

That returns 8 rows, not 6.

North contributes four reps, because both of its pairs tied. South contributes four, because the three reps on 11 all hold second place. Ellis and Jansen are the only two reps this filter drops.

This is the behaviour you want on anything a person will read as a standing. Nobody wants to explain why one of three reps with identical numbers was published as fourth.

It is also the behaviour that surprises a dashboard. If a tile is built for three rows per region and the data ties, RANK hands it eight. That is a design decision, and it is better made on purpose than discovered on a Monday.

Use DENSE_RANK when you want the top N values

First: how many distinct deal counts are there in North, and how many in South?

Three in North, which are 12, 9 and 7. Three in South, which are 15, 11 and 4. DENSE_RANK numbers those values rather than the rows, so asking for dr <= 3 asks for everyone sitting at the top three values.

SELECT rep, region, deals
FROM (
  SELECT rep, region, deals,
         DENSE_RANK() OVER (PARTITION BY region ORDER BY deals DESC) AS dr
  FROM sales
) ranked
WHERE dr <= 3
ORDER BY region, deals DESC;

That returns all 10 rows, because every rep in this table sits at one of their region's top three deal counts. On ten rows that looks like a filter doing nothing. On real data it is the opposite. It is how you say "every customer in the top three spending tiers" without knowing in advance how many customers that is.

Here are the three functions with the same filter, one line each.

One table, one filter, three row counts. That is the whole point of this page in a single line. "Top three" is not a specification until you say which of those three you meant.

Picture your own table for a moment. Take the column you rank on and ask how often two rows share a value in it. If ties are common there, this choice is already changing a number somebody is reading.

Edge cases worth knowing

You cannot filter on the ranking in WHERE. Writing WHERE ROW_NUMBER() OVER (...) = 1 is an error on every engine. Window functions are worked out after WHERE has already run, so the number does not exist yet. That is why every example here wraps the ranking in a subquery or a CTE and filters outside it.

NULL has to be told where to sit. A rep with NULL deals still gets a number. SQLite and MySQL treat NULL as the smallest value, so a descending sort puts it last. PostgreSQL and Oracle treat it as the largest, so the same descending sort puts it first, at the top of your leaderboard. Say what you want with ORDER BY deals DESC NULLS LAST where your engine supports it, rather than inheriting a default that changes when the database does.

PARTITION BY is optional. ORDER BY is not. Leave out PARTITION BY and you rank the whole table, which is often exactly what you want. Leave out ORDER BY and the numbers mean nothing, because nothing decided what first means.

Two more in the same family. NTILE(4) splits the sorted rows into four buckets and labels each row with its bucket, which is how quartiles get built. PERCENT_RANK() gives the place as a fraction between 0 and 1, which is what you want when two groups are different sizes and a raw place would not compare.

Support is universal now, but not ancient. Ranking functions arrived in SQL Server 2005, PostgreSQL 8.4, MySQL 8.0 and SQLite 3.25. If ROW_NUMBER throws a syntax error, check the version before you check the syntax.

Why the numbers behave this way

The three functions are not three versions of one idea. They count three different things, and the names say which.

ROW_NUMBER counts rows. Five rows, five numbers, and no repeat is possible.

RANK reports place, which is one plus the number of rows that beat you. Nothing beats Alvarez or Brennan, so both are 1. Two rows beat Cho, so Cho is 3. The skipped 2 falls out of that definition. It is not a separate rule to remember.

DENSE_RANK counts distinct values, which is one plus the number of distinct values above you. One value beats 9, so 9 is 2.

Running all three at once costs less than it looks. A database answers window functions by sorting the rows once for each distinct partition and sort it sees, then sweeping through in order to produce every function that shares that sort (Leis, Kundhikanjana, Kemper, & Neumann, 2015, Proceedings of the VLDB Endowment, 8(10), 1058–1069). All three here share one ORDER BY deals DESC, so putting all three in a query while you decide is close to free. Comparing them on your own data is cheaper than guessing.

How to apply this to your own work

1. Find one query in your files that returns a top N. Look at the sort column and ask whether two rows can tie on it.
2. If they can, add all three functions to the query and run it once. The column that disagrees with the others tells you what your current query is really doing.
3. Decide from the question, not the code. A fixed number of rows is ROW_NUMBER. A place is RANK. Top values is DENSE_RANK.
4. Give every ROW_NUMBER you keep a tiebreaker column in its ORDER BY, even when a tie looks impossible today.
5. Do not sweep every ranking query in the codebase this week. That job is miserable and mostly wasted. Fix them as they come back up, starting with anything a person reads as a standing.

If you have paper nearby, write the five North rows down the page and number them three times, once per function. Doing it by hand once beats reading the table twice, and the skipped 2 stops being something you have to memorise.

Cheat sheet

The one habit to keep

Before you write a ranking, say what a tie should do out loud. Split it, share the place, or share the value. The function is whichever of those three you just said, and every argument about the number downstream is really an argument about that sentence.

Which of the three is in the query you run most, and did you choose it, or was it the first one you learned?