SQL RANK vs DENSE_RANK vs ROW_NUMBER: How to Choose the Right One

Say you are trying to pull the top three products by revenue for each region, and two products in the same region have identical revenue numbers. Your manager wants to know: do both products get ranked number 2, or does one get number 2 and the other number 3? The answer depends entirely on which ranking function you choose — RANK, DENSE_RANK, or ROW_NUMBER — and most SQL writers pick one without understanding what the other two would have done.

This post answers the six questions I hear most often about these three functions, in the order that matches how people hit the problem in real work. By the end you will know which function to reach for based on the business question, not based on whichever one you happened to see last.

Question 1: What is the core difference between RANK, DENSE_RANK, and ROW_NUMBER?

All three functions assign a sequential number to each row within a partition, based on an ORDER BY that you specify inside the OVER clause. The syntax is identical. The behavior diverges at the moment the data contains ties.

ROW_NUMBER does not care about ties. It assigns a unique number to every single row, in whatever order the database processes them. Ties are broken arbitrarily, and each tied row gets its own consecutive number. If two products both have the same revenue, one will get 2 and the other will get 3 — the database decides which is which, and you cannot predict it without an additional tiebreaker in your ORDER BY.

RANK does care about ties. Tied rows receive the same rank number, and the numbering then skips ahead to account for the tie. Two products tied at rank 2 both get 2, and the next distinct product jumps to rank 4. The gap between 2 and 4 reflects the fact that two rows consumed rank 2 and rank 3.

DENSE_RANK handles ties the same way as RANK — tied rows share a rank — but it never leaves gaps. Two products tied at rank 2 both get 2, and the next distinct product gets rank 3. The sequence stays dense, with no missing numbers.

A small table makes this concrete. Suppose you have five products with revenue values of 100, 90, 90, 80, and 70. The three functions produce this:

The revenue values are the same in every column. Only the numbering rule changes.

Question 2: When should I use ROW_NUMBER instead of RANK or DENSE_RANK?

ROW_NUMBER is the right tool whenever you need exactly one row per group, regardless of whether ties exist in your data. It is the function for “find the most recent order per customer,” “find the newest employee per department,” or “deduplicate this table.”

The pattern is almost always the same: assign ROW_NUMBER partitioned by the group, ordered by whatever determines recency or priority, then wrap the query and filter for row number equal to 1. Because ROW_NUMBER guarantees unique numbers with no ties, the filter always returns precisely one row per partition.

A second common use is pagination. If you are building a page that shows 50 results per page, ROW_NUMBER across the entire result set, ordered by your display order, gives you a stable identifier for each row position. Filtering for positions 51 through 100 gives you page two, and the numbering never skips.

Tiebreaker quality matters here. If you care which of two tied rows gets number 1, add a second column to your ORDER BY. For example, ORDER BY order_date DESC, order_id DESC ensures that when two orders share a timestamp, the one with the higher ID comes first. Without that tiebreaker, the database picks arbitrarily, and your result can change between runs.

Question 3: When should I use RANK instead of the other two?

Reach for RANK when your business question treats tied values as equal in standing, and you also care about how many rows tied at each position. The gap that RANK introduces is not a bug — it is information about the size of the tie.

A leaderboard is the classic case. If two players tie for first place in a competition, the next player is in third place, not second. That gap tells viewers that two people shared the top spot. RANK produces exactly this behavior.

RANK also fits “top N per group” queries where ties at the boundary matter. If you need the top 3 products by revenue per region and two products tie for third place, RANK will include both tied products, potentially returning more than three rows. That is often the correct business outcome — excluding a product that tied for third purely to hit a count of three would misrepresent the data.

The cost of RANK appears when you use it with a WHERE clause that says rank value less than or equal to some number. A rank of 4 might appear even when only three distinct positions exist, because of a tie. If you expected exactly three rows, your query delivers four, and your code needs to handle that gracefully.

Question 4: When should I use DENSE_RANK instead of RANK?

DENSE_RANK is the tool for questions about distinct positions, where the size of the tie does not matter. It answers “how many distinct performance levels exist?” rather than “how many rows are in this group?”

The most common use case is finding the top N distinct values per group. If you need the top 5 revenue values per region — not the top 5 products, but the top 5 distinct revenue levels — DENSE_RANK with a filter for rank less than or equal to 5 returns every row that shares one of those five distinct values. RANK would return the same set of rows, but with larger rank numbers that vary depending on tie sizes, making the filter bound harder to reason about.

DENSE_RANK also shines in reporting where you display a rank column to users. A dense sequence of 1, 2, 3, 4 is far easier to read and interpret than 1, 2, 2, 4, 5. If nobody needs to know that two rows shared rank 2, the gaps just look like data errors.

One caution: DENSE_RANK can surprise you if you assume the output count equals the rank bound. Top 3 distinct values might return 7 rows if multiple rows share each value. That is the correct behavior, but it requires a mental model shift from “top N rows” to “top N distinct values.”

Question 5: How do the three functions behave with partitions and ORDER BY?

All three functions operate within partitions defined by PARTITION BY. The ranking restarts at 1 for every partition. If you partition by region, each region starts its own sequence — product ranks under region A never affect region B. Omit PARTITION BY entirely, and the entire result set becomes a single partition, producing a global ranking.

The ORDER BY clause drives the value ordering. Ascending order ranks the smallest value as 1. Descending order ranks the largest value as 1. This is a common source of off-by-one errors in real queries — people write ORDER BY revenue ASC expecting the highest revenue to be rank 1, and instead their top performer lands at the bottom of the ranking.

The ORDER BY comes before the closing parenthesis of the OVER clause. The syntax pattern is: function name, OVER, open parenthesis, PARTITION BY column_name, ORDER BY other_column DESC, close parenthesis. The PARTITION BY and ORDER BY are both optional, but if you include ORDER BY without PARTITION BY, the ranking applies across the entire result set.

Tie resolution inside the ORDER BY matters more than most people expect. Consider two rows with identical values in your ordering column but different values in other columns. ROW_NUMBER breaks the tie arbitrarily unless you supply an extra ordering column. RANK and DENSE_RANK assign the same rank to both rows, but if RANK skips a number, the skipped value depends on the tie, not on row order. In practice, always add a tiebreaker column to your ORDER BY when you use ROW_NUMBER, and consider adding one for the other two functions to keep output stable across database versions.

Question 6: How do I convert between these functions in mid-query, for example to get an ordered list that restarts ranking?

You cannot change from RANK to DENSE_RANK mid-query; each window function call makes its own decision. You can, however, include multiple window functions in the same SELECT, each with a different function and potentially different ORDER BY clauses. This is a technique worth knowing because it prevents needing to run the query twice.

A single query can compute ROW_NUMBER for deduplication and RANK for business ranking simultaneously. You might use RANK for the displayed rank in a report, and ROW_NUMBER in an outer query to pick exactly one row per group for further processing. The two values coexist fine in the same result set.

To restart ranking based on a group boundary, just change the PARTITION BY. Suppose your current query ranks salespeople across all regions. To rank within each region, add PARTITION BY region to the OVER clause. The function type stays whatever you chose; the partition definition is what resets the sequence.

There is no built-in mechanism for converting one function’s output into another’s semantics after the fact. If you discover the business question needs a different tie-handling rule, edit the query and re-run it. That usually means changing the function name only — the rest of the OVER clause stays identical.

A Decision Checklist Rather Than a Syntax Reference

Instead of memorizing another syntax diagram, work through this sequence of questions. They lead directly to the function you need.

First: does the business question need exactly one row per group, regardless of ties? If yes, use ROW_NUMBER and add a deterministic tiebreaker to your ORDER BY.

Second: do ties represent equal performance, and would skipping a rank confuse or enlighten the reader? If you want the gap that tells how many rows tied, use RANK.

Third: do you care only about distinct performance levels, not tie sizes? Use DENSE_RANK.

Fourth: will you filter the result by a numeric bound (rank less than or equal to N)? If your bound represents distinct levels, DENSE_RANK keeps your filter simple. If it represents a limit on row count, RANK might return extra rows, and ROW_NUMBER returns exactly N.

The table below summarizes the decision for a list of revenue values with ties:

One final test to run next time you are unsure: take a five-row sample with a deliberate tie, run all three functions side by side in a single query, and compare the columns. That result — familiar as the sample table earlier in this post — will explain the difference faster than any documentation page.

Which of the three functions have you used so far, and what kind of data ties have you run into in practice? Leave a comment with your specific ranking question and I will walk through the exact function and PARTITION BY setup for it.