How to Find the Second Highest Salary in an Employee Table?

Extracting the Second Highest Salary from an Employee Database

This guide outlines various methods for retrieving the second highest salary from an employee database table. The optimal approach depends on whether you need only the salary value or also require associated employee details.

One effective strategy leverages Oracle's analytical functions. The choice of function—DENSE_RANK(), RANK(), or ROW_NUMBER()—impacts the results, particularly when dealing with duplicate salary values.

For isolating the second highest salary, DENSE_RANK(), RANK(), or ROW_NUMBER() can be used. For example:

<code class="language-sql">SQL> select sal, rank() over (order by sal desc) as rnk
from
( select distinct sal
from emp )

RNK   SAL
2      3000</code>

However, if you need additional employee information (e.g., employee name), the function selection becomes crucial. ROW_NUMBER() will return the second employee based on salary order, regardless of salary ties. Conversely, RANK() might omit records with RANK = 2 if the highest salary has duplicates.

DENSE_RANK() generally offers a robust solution, maintaining order while assigning identical ranks to employees with matching salaries. The best function depends on the specific needs and desired output.

The above is the detailed content of How to Find the Second Highest Salary in an Employee Table?. For more information, please follow other related articles on the PHP Chinese website!