T-SQL Interview Question: How to Find the Second Highest Salary

When it comes to SQL interviews, one of the most commonly asked and deceptively tricky questions is: “How do you find the second highest salary from an Employee table?”
This question isn’t just about writing a query — it tests your grasp of sorting, filtering, window functions, subqueries, and how well you handle duplicates and NULLs.

Below is the Employee Table Structure and Sample Data

CREATE TABLE dbo.Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    Gender CHAR(1),
    Department VARCHAR(50),
    Salary INT,
    JoiningDate DATE,
    ManagerID INT,
    IsActive BIT
);

GO
INSERT INTO  dbo.Employee VALUES 
(101, 'Shaun Abbot',      'M', 'IT',        88000, '2020-03-15', NULL, 1),
(102, 'Stefee Taylor',   'F', 'HR',        88000, '2020-07-01',  105, 1),
(103, 'Mathew Bulmer',   'M', 'Finance',   75000, '2021-01-20',  104, 1),
(104, 'Jennat Warner',    'F', 'Finance',   98000, '2021-10-05', NULL, 1),
(105, 'Jonathan Perry',     'M', 'HR',        95000, '2019-12-12', NULL, 1),
(106, 'Lisa Devine',     'F', 'IT',        85000, '2021-06-11', 101, 1),
(107, 'Adam Trumen',     'M', 'IT',        50000, '2019-04-01', 101, 1),
(108, 'Sarah Mclean',      'F', 'Marketing', 95000, '2023-01-11', NULL, 1),
(109, 'Steve Maxwell',      'M', 'IT',        75000, '2025-12-01', 101, 0),
(110, 'Georgia Havells',   'F', 'Finance',   45000,   '2025-09-01', 104, 1);

Following are the 5 Most Effective Ways to Find the Second Highest Salary in SQL Server using T-SQL programming

1) Using DENSE_RANK() — Recommended Approach

WITH RankedSalaries AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employee
)
SELECT *
FROM RankedSalaries
WHERE SalaryRank = 2;

Handles tied salaries (e.g. two employees with 88000).
Most robust and readable approach

2) Using Subquery with MAX() and <

SELECT *
FROM Employee
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employee
    WHERE Salary < (
        SELECT MAX(Salary) FROM Employee
    )
);

3) Using Subquery with NOT IN + TOP

SELECT *
FROM Employee
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employee
    WHERE Salary NOT IN (
        SELECT TOP 1 Salary
        FROM Employee
        ORDER BY Salary DESC
    )
);

4) Using TOP with DISTINCT + ORDER BY + OFFSET FETCH

SELECT *
FROM Employee
WHERE Salary = (
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    OFFSET 1 ROW
    FETCH NEXT 1 ROW ONLY
);

5) Using ROW_NUMBER() – This approach might fail to return the correct result.

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employee
)
SELECT *
FROM Ranked
WHERE RowNum = 2;

Not Ideal for Ties
Only returns one row, even if multiple employees share the second highest salary

Related Posts

Leave a Reply Cancel reply