ROW_NUMBER, RANK, DENSE_RANK in SQL
`ROW_NUMBER`, `RANK`, and `DENSE_RANK` are window functions that assign an integer position to each row within a partition based on an `ORDER BY` clause. They produce different values only when two or more rows are tied on the ordering expression.
Before this Window Functions Introduction (OVER, PARTITION BY)
Builds toward NTILE and Percentile Functions, DISTINCT ON
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.
All three require ORDER BY inside OVER. Omitting it is an error. The functions have no basis for assigning positions without a sort key. 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:
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.
For scores 95, 95, 80, 75: ROW_NUMBER → 1, 2, 3, 4. RANK → 1, 1, 3, 4. DENSE_RANK → 1, 1, 2, 3.
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 created_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.
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?
9 ROW_NUMBER, RANK, DENSE_RANK 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.
These problems are part of the ROW_NUMBER, RANK, DENSE_RANK lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.