Find the second-highest salary in employee table in an SQL database

Using the DENSE_RANK() function to find the second-highest salary in an SQL database. Here’s the query:

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) ranked_salaries
WHERE rank = 2;

Explanation:

1. DENSE_RANK(): Assigns a rank to each unique salary in descending order. If there are duplicate salaries, they get the same rank, and the next rank is not skipped.
2. Subquery:
   • Calculates the DENSE_RANK() for each salary in the employees table.
3. Outer Query:
• Filters the rows where the rank is 2 (second highest).

Using DENSE_RANK and a Common Table Expression (CTE), you can write the following SQL query:

WITH RankedSalaries AS (
    SELECT 
        EmployeeID, 
        Salary, 
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT 
    EmployeeID, 
    Salary
FROM RankedSalaries
WHERE Rank = 2;

Explanation:

1. WITH RankedSalaries: This CTE assigns a DENSE_RANK to each salary in descending order. Employees with the same salary receive the same rank.

   • DENSE_RANK() OVER (ORDER BY Salary DESC) generates a ranking based on salaries, where duplicate salaries get the same rank, and the next rank is incremented by 1.

2. SELECT from CTE: The outer query retrieves the employees whose rank is 2, corresponding to the second-highest salary.