How Can Oracle's Analytic Functions Find the Second Highest Employee Salary?

Use Oracle analytic functions to find the second highest employee salary

Finding the second highest salary is a common need in employee data tables. Unlike other programming languages, Oracle provides a series of analytic functions to simplify this task.

Find the predefined function for the second highest salary

Oracle provides some analytical functions that can help find the second highest salary. These include:

• DENSE_RANK()
• RANK()
• ROW_NUMBER()

Select only the second highest salary

If you only need to retrieve the second highest salary, you can use any of the above functions. For example:

<code class="language-sql">SELECT sal
FROM (
  SELECT DISTINCT sal,
         RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
)
WHERE rnk = 2;</code>

Select other employee information

When selecting other employee information, since duplication needs to be considered, the choice of function becomes very important.

ROW_NUMBER()

ROW_NUMBER() will return the second employee in salary order. However, it may skip rows if there is a tie for the highest or second highest salary.

RANK()

RANK() will skip the record if there is a tie for the highest salary. In this case, there will be no records with RANK = 2.

DENSE_RANK()

DENSE_RANK() is usually preferred as it preserves the order of records even in the presence of a tie. It assigns a unique ranking to records that are tied.

Select the second highest salary and name

To select both the second highest salary and employee name, you can use a combination of functions:

<code class="language-sql">SELECT name, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp WHERE sal < (SELECT MAX(sal) FROM emp));</code>

Alternatively, to express the logic more clearly, you can use the following method:

<code class="language-sql">SELECT name, sal
FROM (
    SELECT name, sal, DENSE_RANK() OVER (ORDER BY sal DESC) as rank
    FROM emp
) ranked_salaries
WHERE rank = 2;
</code>

The above is the detailed content of How Can Oracle's Analytic Functions Find the Second Highest Employee Salary?. For more information, please follow other related articles on the PHP Chinese website!