Difference between ROW_NUMBER, RANK and DENSE_RANK

Discover concepts like ROW_NUMBER, RANK and DENSE_RANK.

Suppose we have a table Student

Name                                 Total marks

Shashi                                    200
Aniket                                     400
Dinesh                                    400
Nitesh                                     500 
Ravi                                         700


Exploring row_number()

Now i need to put Serial no. against these student..

i.e.

Name                                 Total marks     Sr.No

Shashi                                    200                  1
Aniket                                     200                  2
Dinesh                                  400                    3
Nitesh                                    500                   4 
Ravi                                       700                    5

Now let's achieve this by SQL query....

select * , row_number() over(ORDER BY  Total marks ) as 'Sr.No'   FROM student

Exploring dense_rank()

Now we wants to provide position to students ...

Name                                 Total marks     Sr.No            Position

Shashi                                   200                  1                   4
Aniket                                    400                  2                   3
Dinesh                                   400                  3                   3
Nitesh                                    500                  4                   2
Ravi                                       700                  5                    1

Here is the SQL query....

select * , dense_rank() over(ORDER BY Total marks) as 'Sr.No'   FROM student

Note: in oder by bracket we need to put only those columns on which bases we wants to position the rank of student

Exploring rank()

Now we wants to provide position to students  but jumps from similar postion rank

Name                                 Total marks     Sr.No            Position

Shashi                                    200                  1                   5   ------->>>>Here 4th position missing as there are two 3rd position
Aniket                                    400                  2                    3
Dinesh                                  400                    3                   3
Nitesh                                    500                   4                   2
Ravi                                       700                    5                   1

Now let's achieve it by SQL query....

select * , rank() over(ORDER BY  Total marks ) as 'Sr.No'   FROM student