Using rank() and dense_rank() to find any salary rank in SQL

Bismillahir Rahmanir Raheem

Table sal
name                                salary
A 1000
B               500
C 600
D 200
E 700
F                                       500

Difference Between -rank and dense_rank

select name,salary,rank() over(order by salary desc)Rank
from sal;

name salary Rank
A 1000 1
E 700 2
C 600 3
B 500 4
F 500 4
D 200 6


select name,salary,dense_rank() over(order by salary desc)Rank
from sal;

name salary Rank
A 1000 1
E 700 2
C 600 3
B 500 4
F 500 4
D 200 5


Now you can find out any rank as your requirement by using a where clause and specifying the rank you desire.

say third most highest

select name,salary
from
(
  select name,salary,rank() over(order by salary desc)Rank
  from sal
)
where Rank=3;