Select Top 3 and nth highest department wise salary from employee master using partition By clause

DENSE_RANK()

Returns the rank of rows within a result set, without any gaps in the ranking. If column on which dense_rank() is calculated has duplicate value then result will have same rank.

i.e:

PARTITION BY

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

Due to Partition by cluase result set is devided by specified column. As in above result set data divided by department and dense_rank is limited to distinct department rows.

1. Select Top 3 salary records from table
   
   
   
   In above query department wise top 3 salaried employee selected.
    
2. Select nth department wise highest salary from result set