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