IKH

Rank Functions – II

In the previous segment, you learnt how rank functions are implemented to rank values based on certain criteria. In the upcoming videos, Shreyas will demonstrate the difference between the ‘rank’ and ‘dense rank’ functions. You will also learn about another type of rank function, which is known as the ‘per cent rank’ function.

In this video, you learnt that there are different types of rank functions, which are as follows:

  • RANK(): Rank of the current row within its partition, with gaps.
  • DENSE_RANK(): Rank of the current row within its partition, without gaps.
  • PERCENT_RANK(): Percentage rank value, which always lies between 0 and 1.

The syntax for writing the ‘dense rank’ and ‘per cent rank’ functions are as follows:

In the upcoming video, you will understand how to use different rank functions in queries to solve various problems.

So, as you saw in the video, the ‘rank’ function need not have consecutive values, but the ‘dense rank’ function must have these values. For example, consider the table given below. It contains the marks obtained by the top three students in the 12th board exams in India conducted by the CBSE board.

CBSE Marks (12th)

Name
Marks (out of 500)
Rank
Dense Rank
Shubham Agarwal49511
Paritosh Sinha49511
Dilip Kumar49232

Notice how Dilip’s rank is 3 but his dense rank is 2. This is because the rank increases whenever the previous entries have similar values. If 10 students, instead of two, had scored 495 marks, then his rank would have become 11, but his dense rank would have remained 2.

There is a fourth type of rank function: the ‘row number’ function. You will learn more about this in the next segment.

Report an error