Ranking

Ranking is done in conjunction with an order by specification.

Suppose we are given a relation student_grades(ID, GPA) giving the grade-point average of each student, and we want to find the rank of each student.

select ID, rank() over (order by GPA desc) as s_rank from student_grades

An extra order by clause is needed to get them in sorted order:

select ID, rank() over (order by GPA desc) as s_rank from student_grades order by s_rank

Ranking may leave gaps: e.g. if 2 students have the same top GPA, both have rank 1, and the next rank is 3.

dense_rank does not leave gaps, so next dense rank would be 2.

Ranking can be done within a partition of the data:

To find the rank of students within each department,

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;

Multiple rank clauses can occur in a single select clause.

Ranking is done after applying group by clause/aggregation

It can be used to find top-n results. It is more general than the limit n clause since it allows top-n within each partition.