SQL ranking assigns a sequential number to rows based on an order you define
SQL ranking lets you number rows in a result set according to values in one or more columns. The most common tool is the RANK() window function, which assigns a number to each row within a partition or across your entire result set. If two rows have identical values in the column you're ranking by, they receive the same rank, and the next rank skips a number — so you might see ranks 1, 2, 2, 4 instead of 1, 2, 3, 4.
Three ranking functions exist in standard SQL: RANK(), DENSE_RANK(), and ROW_NUMBER(). Each handles ties differently. RANK() skips numbers after a tie, DENSE_RANK() does not skip, and ROW_NUMBER() gives every row a unique number regardless of ties. Your choice depends on whether you need consecutive numbers or whether tied rows should share the same rank.
Key Takeaways
- RANK() assigns the same number to rows with identical values and skips the next number, so ties create gaps in the sequence.
- DENSE_RANK() assigns the same number to tied rows but does not skip the next number, keeping the sequence consecutive.
- ROW_NUMBER() gives every row a unique number in order, even if values are identical, and is the only function that guarantees no ties.
- All three functions require an ORDER BY clause inside the OVER() window to define what you are ranking by.
- You can partition your data so ranking restarts for each group — for example, ranking sales by employee within each department separately.
RANK() skips numbers when rows tie
RANK() is the most common choice when you want to rank items by a numeric value and you do not mind gaps in the sequence. If three employees are tied for second place in sales, they all receive rank 2, and the next employee receives rank 4.
The syntax is straightforward: SELECT employee_name, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM employees; This orders employees by sales in descending order and assigns each a rank. The DESC keyword puts the highest sales first, so the top performer gets rank 1.
RANK() is useful when you care about position relative to others — for instance, "who is in the top 10?" — because the rank number itself tells you how many people performed better. If someone has rank 5, exactly four people outperformed them.
DENSE_RANK() keeps numbers consecutive
DENSE_RANK() behaves like RANK() except it does not skip numbers after a tie. If three employees tie for second place, they all get rank 2, and the next employee gets rank 3 instead of rank 4.
Use DENSE_RANK() when you need consecutive numbers and you want tied rows to share a rank. The syntax is identical: SELECT employee_name, sales, DENSE_RANK() OVER (ORDER BY sales DESC) AS rank FROM employees; The only difference from RANK() is the function name.
DENSE_RANK() is common in leaderboards or medal standings where you want to show "first place, second place, third place" without gaps, even if multiple people tie for a position.
ROW_NUMBER() gives every row a unique number
ROW_NUMBER() assigns a unique number to every row, regardless of whether values are identical. If three employees have the same sales total, ROW_NUMBER() still gives them three different numbers — perhaps 2, 3, and 4 — based on the order they appear in the result set.
The syntax is the same: SELECT employee_name, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num FROM employees; When multiple rows have the same ORDER BY value, SQL uses the order they appear in the table to break the tie, which means the result may vary between runs unless you add a tiebreaker column to your ORDER BY clause.
ROW_NUMBER() is useful when you need a simple sequential ID for each row — for example, numbering pages of results or assigning a unique identifier within a sorted set. It is also the only ranking function that guarantees no two rows will have the same number.
Partition your data to restart ranking for each group
The PARTITION BY clause inside the OVER() window lets you restart ranking for each group. Instead of ranking all employees together, you can rank employees within each department separately.
The syntax adds PARTITION BY before ORDER BY: SELECT department, employee_name, sales, RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS rank FROM employees; Now each department has its own ranking starting from 1, and the top salesperson in each department gets rank 1.
PARTITION BY is essential when you need to rank within categories — for instance, ranking products by price within each category, or ranking students by grade within each class. Without it, all rows compete for the same rank numbers.
Comparing the three functions side by side
The difference between the three functions becomes clear with an example. Suppose you have four employees with these sales totals: Alice $100,000, Bob $100,000, Carol $90,000, and David $80,000.
| Employee | Sales | RANK() | DENSE_RANK() | ROW_NUMBER() |
|---|---|---|---|---|
| Alice | $100,000 | 1 | 1 | 1 |
| Bob | $100,000 | 1 | 1 | 2 |
| Carol | $90,000 | 3 | 2 | 3 |
| David | $80,000 | 4 | 3 | 4 |
RANK() gives Alice and Bob both rank 1, then skips to rank 3 for Carol. DENSE_RANK() gives Alice and Bob both rank 1, then goes to rank 2 for Carol. ROW_NUMBER() gives each person a unique number: 1, 2, 3, 4, even though Alice and Bob have identical sales.
Common use cases for ranking in SQL
Ranking is useful whenever you need to identify top or bottom performers, find the nth highest value, or segment data into tiers. A common example is finding the top 3 products by revenue in each category, or identifying which customers are in the top 10% by lifetime spending.
You can also use ranking to find duplicates or to number rows for pagination. For instance, SELECT * FROM results WHERE ROW_NUMBER() OVER (ORDER BY date) BETWEEN 11 AND 20; returns rows 11 through 20 in date order, which is useful for displaying the second page of results.
Another pattern is using ranking to identify the most recent record for each entity. If you have multiple orders per customer, you can rank orders by date and filter for rank 1 to get each customer's most recent purchase.
Frequently Asked Questions
What is the difference between RANK and DENSE_RANK?
RANK() skips numbers after a tie, so if two rows tie for rank 2, the next rank is 4. DENSE_RANK() does not skip, so the next rank is 3. Use RANK() when the gap matters (like "how many people beat me"), and DENSE_RANK() when you need consecutive numbers (like medal positions).
Can I rank by multiple columns?
Yes. Add multiple columns to the ORDER BY clause: RANK() OVER (ORDER BY department, sales DESC) ranks first by department, then by sales within each department. This is different from PARTITION BY, which restarts ranking for each group.
How do I get the top 5 rows using ranking?
Wrap your ranking query in a subquery or use a CTE and filter where rank is 5 or less: WITH ranked AS (SELECT name, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM employees) SELECT * FROM ranked WHERE rank <= 5;
What happens if I use RANK without ORDER BY?
SQL will return an error. The OVER() clause requires an ORDER BY to define what you are ranking by. Without it, the database does not know what order to assign ranks in.
Does ranking work the same in all SQL databases?
RANK(), DENSE_RANK(), and ROW_NUMBER() are part of the SQL standard and work in PostgreSQL, MySQL 8.0 and later, SQL Server, Oracle, and most modern databases. Older versions of MySQL do not support window functions and require workarounds.