How to find an employee with the second highest salary?

Question

Is there any predefined function or method for getting the second highest salary from the employee table?

Answer 1

The way to do this is with Oracle Analytic functions. Your specific scenario is just a solution that I submitted to another thread .

If you just want to choose 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, for example, the name of the employee with the second highest salary, the function you select will affect the result. The main reason for choosing one over the other is what happens when there is a connection.

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

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

Answer 2

In Oracle, you would use such a query, for example, to return paged results (rows from 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 

Answer 3

If you want to find the n highest salaries from the table, you can use this:

 select min(sal)from (select distinct sal from emp order by sal desc) where rownum<=n; 

where n is 1,2,3, ..., n

This is a very simple way to find out the maximum salary from the table.

Answer 4

Try the following:

 SELECT * FROM employee emp WHERE 2=(SELECT COUNT(*) FROM employee WHERE salary >= emp.salary);