RANK and DENSE_RANK in SQL

Level 5185 keystrokes

RANK leaves gaps after ties while DENSE_RANK provides consecutive numbers.

SQL code

-- Return each passing score with two kinds of rank
1SELECT
2 name,
3 score,
-- RANK: ties share a rank and the next rank is skipped (1, 1, 3)
4 RANK() OVER (
-- Higher scores rank first
5 ORDER BY score DESC
6 ) AS rank_num,
-- DENSE_RANK: ties share a rank, with no gap after them (1, 1, 2)
7 DENSE_RANK() OVER (
8 ORDER BY score DESC
9 ) AS dense_rank_num
10FROM
11 exam_results
-- Only scores of 60 or more are ranked; WHERE runs before the window functions
12WHERE
13 score >= 60
-- Show the best scores first
14ORDER BY
15 score DESC;

Output

+-------+-------+----------+----------------+
| name  | score | rank_num | dense_rank_num |
+-------+-------+----------+----------------+
| Alice |    95 |        1 |              1 |
| Bob   |    95 |        1 |              1 |
| Carol |    88 |        3 |              2 |
| Dave  |    82 |        4 |              3 |
+-------+-------+----------+----------------+
4 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 5
  • (× 4
  • )× 4
  • ,× 3
  • ;× 1
  • =× 1
  • >× 1

More SQL examples at level 5

← SQL typing practice