Choose among ROW_NUMBER(), RANK() and DENSE_RANK() based on what “Top-N” means for your query. ROW_NUMBER() <= N returns at most N rows per group, but can split a tie at the cutoff. RANK() <= N keeps ties at competition positions up to N and may return more than N rows. DENSE_RANK() <= N returns the first N distinct ordering values, also potentially more than N rows.
How the three functions treat ties
All three are window functions that assign numbers according to an ordering. Their key difference is whether tied rows share a number and whether the next number skips ahead.
As an Amazon Associate I earn from qualifying purchases.
| Function | What tied rows get | What happens next | Meaning of filtering to <= N |
|---|---|---|---|
ROW_NUMBER() |
A distinct number for every row. The order among ties can be nondeterministic unless the window ordering resolves it. | No gaps: each row receives the next number. | At most N rows per partition; a tie at the cutoff can be split. |
RANK() |
The same rank for peers. | Gaps account for the number of rows sharing the previous rank. | Competition positions through N, including ties at the cutoff; may return more than N rows. |
DENSE_RANK() |
The same rank for peers. | No gaps: the next distinct value advances the rank by one. | The first N distinct ordering values; may return more than N rows. |
For example, with metric values 100, 90, 90, 80, the results are:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Metric | ROW_NUMBER() |
RANK() |
DENSE_RANK() |
|---|---|---|---|
| 100 | 1 | 1 | 1 |
| 90 | 2 or 3 | 2 | 2 |
| 90 | 2 or 3 | 2 | 2 |
| 80 | 4 | 4 | 3 |
The two ROW_NUMBER() results for 90 can swap unless the ordering includes a stable tie-breaker. Filtering to three illustrates the different rules: ROW_NUMBER() <= 3 returns three rows, with the tie split only if the cutoff falls inside its peer group. RANK() <= 3 returns 100 and both 90s; there is no rank 3 because the next competition rank is 4. DENSE_RANK() <= 3 returns all four rows because there are three distinct metric values.
#1 Best Overall
Choose the function that matches the result you need
Use ROW_NUMBER for a fixed row limit
Choose ROW_NUMBER() when you need no more than N individual rows per group, or need to select one row from each group. If reproducibility matters, add a stable unique key after the business sort key. Otherwise, rows tied on the sort key may receive different row numbers across executions or plans.
Use RANK to preserve competition positions
Choose RANK() when equal values should share a place and the next place should reflect how many rows tied. With values 100, 90, 90, 80, the ranks are 1, 2, 2, 4. Filtering through rank N can return more than N rows when the Nth position is tied.
Use DENSE_RANK for the first N distinct values
Choose DENSE_RANK() when you mean the first N distinct metric values or value groups. Ties share a rank, but the next distinct value advances by one without a gap. The result may include many rows for any of those N values.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Write a per-group Top-N query
This SQL pattern selects up to three rows in each category, sorting by metric and then by a unique item identifier:
Rank #3
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;PARTITION BY category restarts numbering for each category. The window’s ORDER BY defines the numbering or ranking order; the final query’s ORDER BY controls how results are displayed. Filtering on the window result is done in the outer query.
To keep every item tied at the third competition position, use RANK() ordered by metric DESC and filter its result to <= 3. To return the three highest distinct metric values, use DENSE_RANK() with the same metric ordering. Keep the peer definition aligned with the requirement: adding a unique ID to the RANK() or DENSE_RANK() window ordering makes otherwise tied metrics separate peers. A final display sort can still include that ID.
Check your database’s window-function rules
The tie and gap behavior is consistent across the cited documentation, but syntax and nondeterminism details vary by database. Use the reference for the target engine and version before relying on a production query.
- SQL Server / Transact-SQL: Microsoft’s documentation requires a window
ORDER BYforROW_NUMBERandRANK; its references describe shared ranks and gaps forRANK, and no gaps forDENSE_RANK. See Microsoft’s ROW_NUMBER reference, RANK (Transact-SQL) and DENSE_RANK (Transact-SQL). - Google BigQuery / GoogleSQL:
ROW_NUMBERdoes not requireORDER BY, but without it row numbering is nondeterministic. Ordering among peers is also nondeterministic. BigQuery documentsRANKas advancing by the number of peers in the previous rank andDENSE_RANKas advancing by one. See GoogleSQL numbering functions. - 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 cover SQL Server, BigQuery and PostgreSQL 17; they are not a complete survey of every SQL dialect.
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.
