Second Highest Salary

Welcome to exploring a classic SQL interview challenge: “How many different ways can you write an SQL statement that fetches the second-highest salary?”

Apparently, I don’t even know how to code this puzzle, which Anonymous kindly pointed out in the comments. So now I have to redo this blog post.

One point of the puzzle is that we are looking for simply the second-highest salary (and not the name(s) of the employee(s) with the second-highest salary). To properly create test data for the scenario of the second-highest salary, we should include test data where we have duplicate salaries.

Given this new clarification, I have found five different methods for producing the second-highest salary.

1. DENSE_RANK() Function
2. TOP 1 and MAX() Function
3. Correlated Sub-Query and DISTINCT
4. MAX and EXCEPT
5. OFFSET and FETCH with DISTINCT

I will also discuss which SQL statements fail to give the proper results.

Also, I won’t delve into detailed code explanations, as I believe the queries are mainly self-explanatory.

However, it’s worth noting that this exercise is an excellent way to illustrate SQL’s flexibility in offering multiple solutions to the same problem. While performance tuning isn’t the focus of this post, I do recommend running each query on a sizable dataset to compare execution speeds and review the generated execution plans. This can be a valuable starting point for understanding how your database management system optimizes different SQL constructs.

So here are my new solutions! Note that I now properly include duplicate data for both the highest and second-highest salaries.

I’d love to hear your thoughts on other techniques.

DROP TABLE IF EXISTS #Employees;
GO

CREATE TABLE #Employees (
EmployeeName  VARCHAR(50),
Salary MONEY
);

INSERT INTO #Employees (EmployeeName, Salary)
VALUES
('Carl Friedrich Gauss', 250000.00),
('Evariste Galois', 250000.00),
('Pierre-Simon Laplace', 150000.00),
('Sophie Germain', 150000.00),
('Leonhard Euler', 100000.00);

--------------------------------------------
--------------------------------------------
--------------------------------------------
--Version 1
--DENSE_RANK and DISTINCT
WITH cte_Rank AS
(
SELECT  DENSE_RANK() OVER (ORDER BY Salary DESC) AS MyRank,
        *
FROM    #Employees
)
SELECT  DISTINCT
        Salary
FROM    cte_Rank
WHERE   MyRank = 2;

--Version 2
--Top 1 and Max
SELECT  TOP 1
        Salary
FROM    #Employees
WHERE   Salary <> (SELECT MAX(Salary) FROM #Employees)
ORDER BY Salary DESC;

--Version 3
--Correlated Sub-Query and Distinct
SELECT  DISTINCT Salary
FROM    #Employees a
WHERE   2 = (SELECT COUNT(DISTINCT b.Salary)
             FROM #Employees b
             WHERE a.Salary <= b.Salary);

--Version 4
--Max and Except
SELECT MAX(salary) AS salary 
FROM #Employees 
WHERE salary IN (SELECT salary FROM #Employees EXCEPT SELECT MAX(salary) FROM #Employees);

--Version 5
--Offset and Fetch with Distinct 
SELECT  DISTINCT Salary
FROM    #Employees
ORDER BY Salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Below are the statements that display incorrect answers for the second-highest salary puzzle. You may see these statements in other SQL writings concerning this puzzle, but they give an incorrect answer when proper test data is given.

--Incorrect Version 1
--Offset and Fetch
SELECT  Salary
FROM    #Employees
ORDER BY Salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

--Incorrect Version 2
--Top 1 and Top 2
SELECT  TOP 1 
        Salary
FROM    (
        SELECT  TOP 2 *
        FROM    #Employees
        ORDER BY Salary DESC
        ) a
ORDER BY Salary ASC;

--Incorrect Version 3
--Min and Top 2
WITH cte_TopMin AS
(
SELECT  MIN(Salary) AS MinTotalPurchaseAmount
FROM   (
       SELECT  TOP 2 Salary
       FROM    #Employees
       ORDER BY Salary DESC
       ) a
)
SELECT  DISTINCT Salary
FROM    #Employees
WHERE   Salary IN (SELECT MinTotalPurchaseAmount FROM cte_TopMin);

--Incorrect Version 4
--Top 1 and Lag
WITH cte_LeadLag AS
(
SELECT  Salary,
        LAG(Salary, 1, NULL) OVER (ORDER BY Salary DESC) AS LagSalary
FROM    #Employees
)
SELECT  TOP 1 Salary
FROM    cte_LeadLag
WHERE   LagSalary IS NOT NULL
ORDER BY Salary DESC;

Happy coding!