Ranking¶

Ranking is possible with a regular SQL query

SELECT ID, (1+(SELECT COUNT(*)
               FROM student_grades B
               WHERE B.GPA > A.GPA)) as s_rank
FROM student_grades A
ORDER BY s_rank

but this is inefficient and ugly. SQL has a better alternative built-in

SELECT ID, dept_name, 
    rank() OVER (partition by dept_name ORDER BY GPA desc)
    AS dept_rank
FROM dept_grades
ORDER BY dept_name, dept_rank

Finds the rank of students within their own department

Note that ranking is done after the group by clause. It can be used to find top n ranks, while being better than the limit keyword because it supports ranking within partitions as well.

Other Ranking¶

percent_rank - calculated the rank within partition
cume_dist - cumulative distribution
row_number - returns the row number, random in presence of duplicates

nulls in ORDER BY¶

SQL allows the user to add nulls first or nulls last at the end of an order by to choose where to place them.

ntile¶

ntile allows breaking a table into n buckets

SELECT ID, ntile(4) OVER (ORDER BY GPA desc) AS quartile
    FROM student_grades