sqloracle-databasetop-n

How to Get Set of Second Highest Values?

Suppose I have the following table:

    employee_id    salary
    34             100
    22             49
    19             49
    29             30
    17             22

And I want to return the set of employees with the second highest salaries (when there are ties), as follows:

   employee_id    salary
   22             49
   19             49

How would I do that?

Solution

  • Use DENSE_RANK:

    SELECT employee_id, salary
FROM
(
    SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr
    FROM yourTable
) t
WHERE dr = 2;