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