# 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.