How to Find the Employee with the Second Highest Salary in a Database?

Efficiently Retrieving the Second Highest Salary from an Employee Database

Database queries often require optimized methods to extract specific data. A common challenge involves identifying the employee holding the second highest salary within an employee table. This task, while seemingly simple, necessitates careful selection of database functions.

Oracle's analytical functions, such as DENSE_RANK(), RANK(), and ROW_NUMBER(), provide effective solutions. These functions assign ranks to data based on a defined order, enabling the isolation of the second highest salary.

Consider an employee salary table. The following query demonstrates one approach:

select * from
  ( select sal, rank() over (order by sal desc) as rnk
     from
      ( select distinct sal from emp )
    )
  where rnk = 2;

This query successfully retrieves the second highest salary. However, the chosen ranking function significantly impacts results when dealing with duplicate salary values.

ROW_NUMBER(), for example, assigns unique ranks, potentially omitting a rank of 2 if multiple employees share the highest salary. DENSE_RANK(), on the other hand, handles ties more effectively by assigning consecutive ranks, ensuring all distinct salary values are represented in the ranking. Therefore, DENSE_RANK() is often the preferred choice for its robustness in these situations.

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