iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use ROW_NUMBER() when you need at most a fixed number of individual rows, RANK() when ties should share competition positions, and DENSE_RANK() when you want the first N distinct ordering values. The last two can return more than N rows; that is expected when ties are included.
How the three functions handle ties
Window functions assign numbers within an ordered set. If you add PARTITION BY, numbering or ranking restarts for each group. The functions differ in whether equal ordering values share a position and whether the next position skips numbers.
| Function | How ties are handled | What <= N selects |
Typical use |
|---|---|---|---|
ROW_NUMBER() |
Every row receives a distinct number. Rows tied on the ordering columns may be numbered in an unpredictable order unless the ordering resolves the tie. | At most N rows per partition. A tie at the cutoff is split. | A fixed row limit or one selected row per group. |
RANK() |
Tied rows share a rank; later ranks skip ahead by the number of tied rows. | The first N competition positions, including ties at the cutoff; may exceed N rows. | Leaderboards where tied entries share places. |
DENSE_RANK() |
Tied rows share a rank; the next distinct value gets the next consecutive rank. | The first N distinct ordering-value groups; may exceed N rows. | The top N distinct scores or metric values. |
For values 100, 90, 90, 80, the functions assign ROW_NUMBER() values 1, 2, 3, 4 in some order among tied rows; RANK() values 1, 2, 2, 4; and DENSE_RANK() values 1, 2, 2, 3. The gap in RANK() reflects the two rows occupying position 2. Microsoft’s RANK reference and DENSE_RANK reference document these distinctions for SQL Server.
Why Top-N queries can return extra rows
Filtering a ranking result to <= 3 does not mean the same thing for all three functions. With the example values above, ROW_NUMBER() <= 3 returns three rows, but which tied rows land inside that limit depends on their ordering. RANK() <= 3 returns the 100 and both 90 rows: there is no rank 3 because the 90s tie at rank 2 and the next rank is 4. DENSE_RANK() <= 3 returns all four rows because there are three distinct values.
So an apparent “duplicate” in a Top-N result may be a tied row, not a duplicate record. If the requirement is exactly N individual rows, use ROW_NUMBER() and deliberately decide which tied row wins. If the requirement is to preserve ties or include N distinct values, the larger result is part of the requested rule.
#1 Best Overall
Choose the ordering to match the requirement
Exactly N rows per group
Use ROW_NUMBER() and add a stable, unique tie-breaker after the business sort key. For example, to get up to three items per category, with lower item_id resolving equal metrics:
WITH ranked AS (
SELECT
category,
item_id,
metric,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY metric DESC, item_id
) AS rn
FROM items
)
SELECT category, item_id, metric
FROM ranked
WHERE rn <= 3
ORDER BY category, metric DESC, item_id;Here PARTITION BY category restarts the row numbers for each category. The unique item_id makes the choice repeatable when metrics tie. The final ORDER BY controls how results are displayed; it does not change which rows the window function selected.
All rows tied within the top N competition positions
Use RANK(), ordered only by the value that defines a tie. For example, if all items sharing the third competition position must be included, rank by metric DESC and filter to rank 3 or lower. Do not add a unique ID to this window ordering: that would break metric ties into separate ranks.
The top N distinct values
Use DENSE_RANK() ordered by the metric, then filter to <= N. This includes every row whose metric is among the N highest distinct metric values, even if several rows have the same value.
Check the database dialect before relying on ordering behavior
The tie and gap rules are consistent across the cited references, but syntax requirements and how unspecified ordering is described vary by database. Use documentation for the database and version that will run the query.
- SQL Server: Microsoft documents a window
ORDER BYforROW_NUMBERandRANK. Its references describe the shared-rank and gap behavior forRANKand the no-gap behavior forDENSE_RANK. See the ROW_NUMBER documentation, RANK documentation, and DENSE_RANK documentation. - BigQuery (GoogleSQL):
ROW_NUMBER()can be used without anORDER BY, but row numbering is then nondeterministic; ordering among peers is also nondeterministic. Google documentsRANK()as advancing by the number of peers at the prior rank andDENSE_RANK()as advancing by one. See Google’s numbering-functions reference. - PostgreSQL 17: The official reference describes
rankas having gaps anddense_rankas having none; window functions depend on the window’s sort ordering. See PostgreSQL 17 window functions.
These references are examples, not a complete survey of SQL dialects. In production, verify the target database’s syntax and determinism rules rather than assuming one vendor’s behavior is universal.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
