ROW_NUMBER() / RANK() / DENSE_RANK()

Ranking functions assign a position number to every row in an ordered result - the difference is in how they handle ties: one gives every row a unique number regardless of ties, one lets tied rows share a number but skips the next spot.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Ranking Retail Orders with ROW_NUMBER(), RANK(), and DENSE_RANK()

SELECT order_id, category, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, order_id) AS row_num,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk
FROM orders

Result:

order_id	category	revenue	row_num	rnk	dense_rnk
O2001	Electronics	320.0	1	1	1
O2002	Electronics	320.0	2	1	1
O2003	Electronics	275.5	3	3	2
O2004	Clothing	145.0	1	1	1
O2005	Clothing	145.0	2	1	1

Step by step

AS gives a column a friendlier name in the result.

SELECT order_id, category, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, order_id) AS row_num,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk

FROM tells SQL which table to pull the data from.

SELECT order_id, category, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, order_id) AS row_num,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk
FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that adds a sequential number to each row, ordered by revenue descending.

SELECT *, ROW_NUMBER() OVER (ORDER BY revenue DESC) FROM orders

2. Using store_sales, write a query that adds a rank without gaps to each row, ordered by revenue descending.

SELECT *, DENSE_RANK() OVER (ORDER BY revenue DESC) FROM store_sales

3. Using products, write a query that adds a sequential number to each row, ordered by price descending.

SELECT *, ROW_NUMBER() OVER (ORDER BY price DESC) FROM products

4. Using employees, write a query that adds a rank with gaps to each row, ordered by salary descending.

SELECT *, RANK() OVER (ORDER BY salary DESC) FROM employees

5. Using reviews, write a query that adds a rank without gaps to each row, ordered by rating descending.

SELECT *, DENSE_RANK() OVER (ORDER BY rating DESC) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed