How To Use RANK() Function In SQL

In this article, we will learn how to use the RANK() function with a SQL select query.

The RANK() is a window function that returns a unique rank number for each distinct row within a partition of a result set.

It starts with 1 for the first row in each partition and the same column values receive the same ranks. The rank of the next row is not consecutive when multiple rows share the same rank. This is similar to Olympic medaling in that if two athletes share the gold medal, there is no silver medal.

Syntax

Select column_name(s),
    RANK() OVER([PARTITION BY expression(s)] ORDER BY column(s))
From TABLE_NAME;

Note: The RANK() function must have an OVER clause.

OVER Clause

The OVER clause is used to determine which rows from the query are applied to the function, what order they are evaluated in by that function, and when the function’s calculations should restart.

PARTITION BY Clause

The PARTITION BY is a subclause of the OVER clause. The PARTITION BY clause is used to divide a query’s result set into partitions. The RANK() function is applied to each partition separately and reinitialize the row number for each partition.

Example-1 (with using PARTITION BY)

Select *,
    RANK() OVER(PARTITION BY age ORDER BY age) as Rank
From StudentInfo;

Output:

Here, the PARTITION BY with RANK() function has no special meaning,  as the rank will be done according to Student’s age values per each partition, and the data will be partitioned according to the Student’s age values.

Example-2 (without using PARTITION BY)

Select *,
    RANK() OVER(ORDER BY age desc) as Rank
From StudentInfo;

Output:

 

Also, check How To Use ROW_NUMBER() Function In SQL