Ranking Functions in MS/SQL

Ranking Functions in MS/SQL is used to assign the ranks to each row wise & group of row wise.

There are three types of Ranking Functions:

1. ROW_NUMBER()

2. RANK()

3. DENSE_RANK()

ROW_NUMBER( )

Row number is used to give a sequential number for each row in the data. The first row is given rank one and it keeps in incrementing. A different rank is given to each and every row even when the values are equal.

SELECT NAME, CATEGORY, PRICE, ROW_NUMBER() OVER (ORDER BY PRICE DESC) AS RANKS FROM PLANTS;

ROW_NUMBER

RANK( )

RANK() SQL Rank function is used to specify rank for each row in the result set. We have a table of plants which their name and price. We want to rank the plants as per their price. For example, in the following screenshot, plant ORCHID has the highest price so will get the first rank.

SELECT NAME, CATEGORY, PRICE, RANK() OVER (ORDER BY PRICE DESC) AS RANKS FROM PLANTS;

RANK()

Here you can see that when the price of plants is equal they get the same rank but it is not in a sequential manner because that repeated rank is missed. This can be fixed when we use DENSE_RANK()

DENSE_RANK( )

DENSE_RANK() function is used to specify a unique rank number within the specified column value. This does not skip the number for similar values.

SELECT NAME, CATEGORY, PRICE, DENSE_RANK() OVER (ORDER BY PRICE DESC) AS RANKS FROM PLANTS;

DENSE_RANK()