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 rank1SELECT2 name,3 score, -- RANK: ties share a rank and the next rank is skipped (1, 1, 3)4 RANK() OVER ( -- Higher scores rank first5 ORDER BY score DESC6 ) 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 DESC9 ) AS dense_rank_num10FROM11 exam_results-- Only scores of 60 or more are ranked; WHERE runs before the window functions12WHERE13 score >= 60-- Show the best scores first14ORDER BY15 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. 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