How to find the employee with the second highest salary?

Is there any predefined function or method available to get the second highest salary from an employee table?

Solution 1:

The way to do this is with Oracle's Analytic functions. Your particular scenario is just a variant on the solution I provided in another thread.

If you are interested in just selecting the second highest salary then any of DENSE_RANK(), RANK() and ROW_NUMBER() will do the trick:

SQL> select * from
  2   ( select sal
  3            , rank() over (order by sal desc) as rnk
  4     from
  5      ( select distinct sal
  6        from emp )
  7    )
  8  where rnk = 2
  9  /

       SAL        RNK
---------- ----------
      3000          2

SQL> 

However, if you want to select additional information, such as the name of the employee with the second highest salary, the function you choose will affect the result. The main reason for choosing one over another is what happens when there is a tie.

If you use ROW_NUMBER() it will return the second employee ordered by salary: what if there are two employees tying for the highest salary? What if there are two employees tying for the second highest salary? Wheareas if you use RANK() and there are two employees tying for first highest salary, there will be no records with RANK = 2.

I suggest DENSE_RANK() is the usually the safest function to choose in these cases, but it really does depend on the specific business requirement.

Solution 2:

In Oracle you would use a query such as this one to return paged results (rows M to N):

SELECT NAME, salary
  FROM (SELECT NAME, salary, ROWNUM r 
          FROM (SELECT NAME, salary 
                  FROM employee 
                 ORDER BY salary DESC
                )
         WHERE ROWNUM <= :N
        )
 WHERE r >= :M

Alternatively, you can use analytics:

SELECT NAME, salary
  FROM (SELECT NAME, salary, row_number() over (ORDER BY salary DESC) n 
          FROM employee)
 WHERE n BETWEEN :M AND :N