Both RANK and DENSE_RANK are window functions that operate on a partition (or window) of a result set.
RANK function ranks every row of the result set – with gaps. That is, if multiple rows tie for the same rank, the next number(s) in ranking will be skipped.
Example: If 2 rows tie for rank 1, the next rank assigned will be 3. (Rank 2 will be skipped)
Syntax: Below is the query to rank students based on their test scores in SCORES table using rank function.
SELECT student_id, test_score, rank() over(order by test_score desc) as student_rank FROM SCORES;
Below is the syntax to view student ranks by course:
SELECT student_id, course_id, test_score, rank() over(partition by course_id order by test_score desc) as student_rank FROM SCORES;
DENSE_RANK function ranks every row of the result set – without gaps. That is, if multiple rows tie for the same rank, the next number in ranking will be assigned to the next row in order.
Example: If 2 rows tie for rank 1, the next rank assigned will be 2.
Syntax: Below is the query to rank students based on their test scores in SCORES table using dense_rank function.
SELECT student_id, test_score, dense_rank() over(order by test_score desc) as student_rank FROM SCORES;
Similarly, you can use the below syntax to rank students partitioned by course using dense_rank function.
SELECT student_id, course_id, test_score, rank() over(partition by course_id order by test_score desc) as student_rank FROM SCORES;