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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://vikram-bajaj.gitbook.io/cs-gy-6083-principles-of-database-systems/sql/advanced-sql/ranking.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.