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:

Dens Rank Use in Sql

PARTITION BY

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

Partition By in Sql

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

    Selecting Top 3 Rows

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

    Selecting nth Row
Ebook Download
View all
Learn
View all