Fetching the Second Highest Salary Using a Subquery

Explanation

To fetch the second highest salary from a table, we can use a subquery approach that:

1. First finds the maximum salary (highest salary)
2. Then finds the maximum salary that is less than the highest salary

This approach is efficient and works in most SQL databases.

SQL Query

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

How It Works

1. The inner subquery (SELECT MAX(salary) FROM employees) finds the highest salary in the table
2. The outer query then finds the maximum salary that is less than this highest salary
3. This effectively gives us the second highest salary

Example with Dummy Data

Let's create a sample table and demonstrate this:

-- Create a sample employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert dummy data
INSERT INTO employees VALUES 
(1, 'John Doe', 75000.00),
(2, 'Jane Smith', 85000.00),
(3, 'Mike Johnson', 92000.00),
(4, 'Sarah Williams', 92000.00),  -- Same salary as Mike
(5, 'David Brown', 68000.00),
(6, 'Emily Davis', 105000.00);     -- Highest salary

Now, let's run our query:

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

Expected Output

second_highest_salary
---------------------
92000.00

Alternative Approach Using DENSE_RANK (for databases that support window functions)

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

This alternative approach is useful when you want to handle ties (duplicate salaries) consistently.

Notes

• If there are multiple employees with the second highest salary, this query will still return just one value (92000.00 in our example)
• If you want to see all employees who earn the second highest salary, you would modify the query slightly:

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
);