Analytic Functions - RANK() vs DENSE_RANK() vs ROW_NUMBER()

This is a quick reminder - when to use each funcion.

[RANK()| DENSE_RANK()| ROW_NUMBER(])  OVER ([ query_partition_clause ] order_by_clause)
order by:
ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]

the RANK() function

The RANK() function calculates the rank of a value in a set of values. It will skip rank in case of ties. [A A A B C D] => [1 1 1 4 5 6]

the DENSE_RANK() function

Unlike the RANK() function, the DENSE_RANK() function returns rank values as consecutive integers. It does not skip rank in case of ties. Rows with the same values for the rank criteria will receive the same rank values. [A A A B C D] => [1 1 1 2 3 4]

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

the ROW_NUMBER() function

Assign a unique sequential integer starting from 1 to each row in a partition or in the whole result. [A A A B C D] => [1 2 3 4 5 6]

An example

the data

SELECT semester, course, class, student_id ,grade 
FROM student_grades
where semester='2015a'

###

SELECT  class, student_id ,grade,
  RANK()       OVER( PARTITION BY semester, course, class ORDER BY grade DESC) AS rank,
  DENSE_RANK() OVER( PARTITION BY semester, course, class ORDER BY grade DESC) AS dense_rank,
  ROW_NUMBER() OVER( PARTITION BY semester, course, class ORDER BY grade DESC) AS row_number
FROM student_grades
where semester='2015a';