RANK & DENSE_RANK Functions

Part of the Fundamentals section of Coddy's SQL journey — lesson 65 of 72.

ROW_NUMBER() is one type of ranking function, and there are two more: RANK() and DENSE_RANK().

The RANK() function numbers rows like ROW_NUMBER(), but it gives identical numbers for the same rows and skips numbers. DENSE_RANK() is similar to RANK(), but it does not skip numbers.

For example:

id	level
1	5
2	6
3	6
4	7
5	7
6	5

SELECT id, 
	ROW_NUMBER() OVER (ORDER BY level) as row_num,
	RANK() OVER (ORDER BY level) as row_rank,
	DENSE_RANK() OVER (ORDER BY level) as row_dense_rank
FROM table1

This will return:

id	row_num	row_rank	row_dense_rank
1	1	1	1
6	2	1	1
2	3	3	2
3	4	3	2
4	5	5	3
5	6	5	3

Explanation:

ROW_NUMBER(): Always unique: 1, 2, 3, 4, 5, 6
RANK():
- level 5 (2 rows): both get rank 1
- level 6 (2 rows): both get rank 3 (skips 2)
- level 7 (2 rows): both get rank 5 (skips 4)
DENSE_RANK():
- level 5 (2 rows): both get rank 1
- level 6 (2 rows): both get rank 2 (no skip)
- level 7 (2 rows): both get rank 3 (no skip)

Challenge

Easy

Available tables and columns:
medals: region, medal_color, medal_count

Create a query that shows the region, total medal count (sum of all medals regardless of color), and two types of rankings:

A regular_rank (with gaps) over the sum medals
A dense_rank (without gaps) over the sum of medals

Order result by:

order by total_medals desc, region asc

Cheat sheet

SQL provides three ranking functions that behave differently with tied values:

ROW_NUMBER() - assigns unique sequential numbers
RANK() - assigns same rank to tied values and skips subsequent ranks
DENSE_RANK() - assigns same rank to tied values without skipping ranks

For example:

id	level
1	5
2	6
3	6
4	7
5	7
6	5

SELECT id, 
	ROW_NUMBER() OVER (ORDER BY level) as row_num,
	RANK() OVER (ORDER BY level) as row_rank,
	DENSE_RANK() OVER (ORDER BY level) as row_dense_rank
FROM table1

This will return: