How Can I Find the Second Highest Salary in SQL Using Analytic Functions?

Use SQL analytic functions to find the second highest salary

How to easily retrieve the second highest salary from the employees database table?

Answer:

Oracle's analytic functions provide a solution for this task. The following SQL query utilizes these functions to identify the second highest salary:

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

This query utilizes the DENSE_RANK() function to assign consecutive rankings to salaries in descending order. The WHERE clause then filters the results to retrieve the row with rnk = 2, which represents the second highest salary.

Other notes:

When selecting other information, the choice of analysis function will affect the results. In case of a tie, RANK() will skip the ranking and ROW_NUMBER() will return the next employee. In this case, DENSE_RANK() is usually preferred.

The choice of analysis function ultimately depends on the specific business needs and how to handle tie situations.

The above is the detailed content of How Can I Find the Second Highest Salary in SQL Using Analytic Functions?. For more information, please follow other related articles on the PHP Chinese website!