Interview #111: SQL: How would you find the second highest salary in an employee table?

Finding the second highest salary in an employee table is a common SQL interview question. There are multiple ways to achieve this, depending on the SQL dialect and database being used. Below, we will explore different approaches along with their advantages and disadvantages.

Disclaimer: For QA-Testing Jobs, WhatsApp us @ 91-9606623245

Table Structure

Assume we have an employees table with the following schema:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);        

Sample Data

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 7000),
(3, 'Charlie', 6000),
(4, 'David', 8000),
(5, 'Eve', 7000);        

Method 1: Using LIMIT with OFFSET (Best for MySQL, PostgreSQL)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;        

Explanation

1. DISTINCT salary ensures we consider unique salaries.
2. ORDER BY salary DESC sorts salaries from highest to lowest.
3. LIMIT 1 OFFSET 1 skips the highest salary and retrieves the second highest.

Pros

? Simple and easy to understand.

? Efficient for smaller datasets.

Cons

? Does not work in SQL Server (which does not support LIMIT).

? Not optimal for large datasets due to sorting and offsetting.

Method 2: Using SUBQUERY with MAX() (Standard SQL Approach)

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);        

Explanation

1. Finds the highest salary using MAX(salary) in a subquery.
2. Filters out this maximum value and finds the next highest using another MAX(salary).

Pros

? Works in MySQL, PostgreSQL, SQL Server, and Oracle.

? More efficient than LIMIT for large datasets.

Cons

? Returns NULL if there is only one unique salary.

? Can be slow for large datasets if an index is not present on salary.

Method 3: Using DENSE_RANK() (Best for SQL Server & Advanced Analytics)

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

Explanation

1. DENSE_RANK() assigns a ranking to each unique salary.
2. It ensures that duplicate salaries (like 7000 appearing twice) get the same rank.
3. We filter the row where rnk = 2, meaning the second highest salary.

Pros

? Works in SQL Server, PostgreSQL, and Oracle.

? Handles duplicate salaries properly.

Cons

? Might not work in older MySQL versions (MySQL 8+ supports DENSE_RANK()).

? Slightly more complex than the previous approaches.

Method 4: Using ROW_NUMBER() (If You Want the Exact Second Row)

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

Difference from DENSE_RANK()

• ROW_NUMBER() gives a unique rank for each row, even if salaries are the same.
• If two employees have the highest salary, this method might skip the second highest salary.

Pros

? Works in modern databases like SQL Server, PostgreSQL, MySQL 8+, and Oracle.

? Useful when strict row positioning is needed.

Cons

? Not ideal for duplicate salaries (it might skip valid second-highest values).

Method 5: Using NTH_VALUE() (Best for Getting the Nth Salary)

SELECT DISTINCT NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) AS second_highest_salary
FROM employees;        

Explanation

• NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) fetches the second highest salary directly.
• It is best suited for finding any specific rank (not just the second).

Pros

? Clean and efficient for analytics queries.

? Works well for large datasets.

Cons

? Only available in PostgreSQL and Oracle.

? Not available in MySQL or SQL Server.

Handling Edge Cases

1. What if there's only one salary in the table? Most methods will return NULL. You can use COALESCE() to return a default value:

SELECT COALESCE(
    (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)), 
    'No second highest salary'
) AS second_highest_salary;        

1. What if all salaries are the same? All methods will return NULL (except DENSE_RANK() which returns the same value).

Performance Considerations

• Indexes: If salary is indexed, methods using MAX(), LIMIT, or ORDER BY will perform faster.
• Large Datasets: DENSE_RANK() and LIMIT OFFSET can be slower than a subquery with MAX().
• Partitioning: For distributed databases, partitioning can improve query efficiency.

Conclusion: Which Method Should You Use?

?? For MySQL: Use LIMIT OFFSET or MAX() with Subquery.

?? For SQL Server: Use DENSE_RANK() or MAX() with Subquery.

?? For PostgreSQL & Oracle: Use NTH_VALUE() or DENSE_RANK().

By choosing the right approach based on your database and performance needs, you can efficiently retrieve the second highest salary from the employee table. ??