ROW_NUMBER, RANK, DENSE_RANK in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Window Functions Introduction (OVER, PARTITION BY)
Builds toward NTILE and Percentile Functions, DISTINCT ON
What are Ranking Functions in SQL?
ROW_NUMBER, RANK, and DENSE_RANK each assign a number to every row based on its position in an ordering. They produce identical results when there are no ties. The difference only shows when two rows are equal on the sort column.
You're building a leaderboard for sales reps, ranked by total revenue with the top performer at position 1. Most of the time the rankings are clear: one person per position. But some months two reps finish with identical totals. Which function you use determines what the tied rows see.
Do ranking functions need ORDER BY inside OVER?
All three need ORDER BY inside OVER to mean anything. PostgreSQL will run them without one, and that is worse than an error: you get numbers handed out in whatever order the rows happened to arrive, with nothing to tell you the ranking is arbitrary. PARTITION BY is optional: omit it to rank across the entire result; include it to restart ranking within each group.
Here's all three side by side:
SELECT c.name AS customer_name, o.total_amount, ROW_NUMBER() OVER (ORDER BY o.total_amount DESC) AS row_num, RANK() OVER (ORDER BY o.total_amount DESC) AS rnk, DENSE_RANK() OVER (ORDER BY o.total_amount DESC) AS dense_rnk FROM orders o JOIN customers c ON o.customer_id = c.id
ROW_NUMBER assigns a unique integer to every row. If two rows tie, one gets 1 and the other gets 2 — the order between them is arbitrary, but the numbers are always unique with no gaps.
RANK gives tied rows the same number, then skips ahead. Two rows tied for first both get 1. The next row gets 3, because two positions have been occupied by the tie.
DENSE_RANK also gives tied rows the same number, but doesn't skip. Two rows tied for first both get 1. The next row gets 2. The sequence is always consecutive.
How do ROW_NUMBER, RANK and DENSE_RANK differ on ties?
For scores 95, 95, 80, 75: ROW_NUMBER → 1, 2, 3, 4. RANK → 1, 1, 3, 4. DENSE_RANK → 1, 1, 2, 3.
How do you get the latest row per group in SQL?
The most useful pattern with ROW_NUMBER is the "latest row per group": the most recent order per customer, the latest status per account. Partition by the entity ID, order by timestamp descending, then filter for rn = 1 in an outer query:
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
FROM orders
) ranked
WHERE rn = 1This picks one row per customer regardless of ties. Which tied row is selected is not deterministic unless you add a tiebreaker column to the ORDER BY.
Should you use RANK or DENSE_RANK?
The one thing that trips people up: choosing the wrong function for the job.
Use ROW_NUMBER when you need a unique identifier per row — especially for the "one row per group" filter pattern. Use RANK or DENSE_RANK when you need to present standings that reflect ties. DENSE_RANK tells you how many distinct positions precede a row. RANK tells you how many rows precede it.
Three rows have scores 100, 100, and 80. What values do RANK() and DENSE_RANK() assign to the third row?
Practice Ranking Functions in SQL
Brightlane's fulfillment dashboard assigns every order a unique position based on order value for priority processing.
Write a query to return the ID and total amount of every order, plus a unique sequential number assigned in descending order of total_amount.
Assumptions:
- The
orderstable has one row per order with anidand atotal_amount. - The position is
1for the highesttotal_amountand increments by1for each subsequent order. - Every order receives a different position; if two orders share the same
total_amount, they still receive consecutive numbers in some order.
Output:
- One row per order, with columns
id,total_amount, androw_num.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Ranking Functions practice problems
Write a query to return the ID and total amount of every order, plus a unique sequential number assigned in descending order of total_amount.
Write a query to return the ID, name, and price of every product, plus the product's rank by price in descending order.
Write a query to return the ID, name, and price of every product, plus the product's tier number by price in descending order.
Write a query to return the ID, customer ID, and total amount of every order, plus a sequential number within that customer's orders, ordered by total_amount in descending order.
Write a query to return the ID, name, category ID, and price of every product, plus the product's rank within its category by price in descending order.
Write a query to return the ID, name, department ID, and hire date of every employee, plus the employee's seniority rank within their department.
Write a query to return the ID and event count of every session, plus the session's dense rank by event_count in descending order.
Write a query to return the ID, name, and price of every product, plus three position values for each row: a unique sequential position (row_num), a tie-sharing position with gaps after ties (rnk), and a tie-sharing position without gaps (dense_rnk). All three positions are ordered by price in descending order. Sort the final result by price in descending order.
Write a query to return the ID, customer ID, and total amount of every order, plus the order's dense rank within that customer's orders by total_amount in descending order. Sort the final result by customer_id ascending and total_amount descending.
Start learning to practice all 9 Ranking Functions problems, with instant grading and mastery tracking.
Common questions about Ranking Functions
Can RANK leave gaps in the numbering?
Yes, and that is what separates it from DENSE_RANK. Two rows tied at the top both get one, and the next row gets three because two positions are already taken. DENSE_RANK would call that row two instead.
Does ROW_NUMBER give a stable answer when rows tie?
Not on its own. Tied rows get different numbers, but which one is first is arbitrary and can change between runs. Add a tiebreaker column to the ORDER BY when the answer has to be reproducible, which it does whenever you filter for the first row per group.
Can you rank inside groups rather than across the whole table?
Yes, with PARTITION BY. The numbering restarts at one for every group, which is how you rank products within each category instead of against the entire catalogue.
