How to Find Second Highest Salary in SQL using Subquery?

Technical interviews contains question like, “How to Find Second Highest Salary in SQL using Subquery?” to assess a candidate’s problem-solving abilities and grasp of core SQL concepts.

Imagine yourself in an interview. The interviewer asks, “Write a SQL query to find the second highest salary in the ‘Employee’ table.” Time to showcase your SQL expertise by cracking this classic query.

Table of Contents

How to Find Second Highest Salary in SQL using Subquery

SELECT
    MAX(Salary)
FROM
    Employee
WHERE
    Salary < (
        SELECT
            Max(Salary)
        FROM
            Employee
    );

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

This guide will help you to answer the asked questions with different approached but before that we need to create an Employee table and add some data to it to practice the query.

SQL to Create Employee Table

Here is the code, you need to open Microsoft SQL Server Management Studio, already installed on your system, select any test database, or create and select, click on new query to write and execute the query.

Copy code
CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

The above script will create an Employee table in your selected database, once the database is create now we need to add some sample data to it, use the following script for that purpose.

INSERT INTO Employee (EmpID, Name, Salary)
VALUES
    (1, 'John', 50000),
    (2, 'Alice', 45000),
    (3, 'Bob', 55000),
    (4, 'Emma', 60000),
    (5, 'Michael', 48000),
    (6, 'Sophia', 60000),
    (7, 'Olivia', 52000),
    (8, 'William', 48000),
    (9, 'James', 62000),
    (10, 'Emily', 52000),
    (11, 'David', 53000),
    (12, 'Mia', 59000),
    (13, 'Ethan', 51000),
    (14, 'Charlotte', 56000),
    (15, 'Daniel', 63000),
    (16, 'Isabella', 54000),
    (17, 'Matthew', 61000),
    (18, 'Ava', 52000),
    (19, 'Alexander', 58000),
    (20, 'Sophie', 53000),
    (21, 'Benjamin', 64000),
    (22, 'Amelia', 55000),
    (23, 'Jacob', 62000),
    (24, 'Grace', 57000),
    (25, 'Michaela', 53000),
    (26, 'William', 65000),
    (27, 'Liam', 56000),
    (28, 'Ella', 63000),
    (29, 'Aiden', 54000),
    (30, 'Lily', 66000);

Here is the screenshot that is shown after running the above script.

Find Second Highest Salary in the Employee table

SELECT
    MAX(Salary)
FROM
    Employee
WHERE
    Salary < (
        SELECT
            Max(Salary)
        FROM
            Employee
    );

Find Employee with Second Highest Salary

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

Exploring Alternate Approaches: Beware the Pitfalls

While tempting, using TOP and ORDER BY DESC to find the second highest salary in SQL can lead to oversights. For instance, if multiple employees share the second highest salary, employing TOP 1 might overlook valuable data.

Exploring Alternate Concepts

Using ROW_NUMBER()

One approach involves leveraging the ROW_NUMBER() function to assign a unique rank to each salary in descending order. By filtering for rows with a rank of 2, we can pinpoint the second highest salary.

	SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employee
) AS RankedEmployees
WHERE SalaryRank = 2;

Employing Common Table Expressions (CTEs)

Another method utilizes Common Table Expressions (CTEs) to first identify the maximum salary, and then filter for the highest salary below it.

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

Second Highest Salary in MySQL

In MySQL, the LIMIT clause is typically used to constrain the number of rows returned by a query. However, using LIMIT alone to find the second highest salary can be tricky, especially when dealing with ties for that position. The query might inadvertently overlook additional records sharing the same salary.

SELECT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1, 1;

Nth Highest Salary in SQL

Your task is to devise a SQL query to find the nth highest salary. Use your SQL skills to explore various techniques and strategies to accomplish this challenge. Share your solutions and insights in the comments below.

Conclusion | How to Find Second Highest Salary in SQL using Subquery

In this blog post, we discussed the solution of the question “How to Find Second Highest Salary in SQL using Subquery?”

While there are various approaches to solve this problem, understanding subqueries is fundamental for SQL developers and candidates preparing for interviews.

If you have a confusion where database queries are used in software testing, we would suggest you to visit our this blogs, that will clear your confusion.

Feel free to explore other methods and experiment with different SQL functionalities to enhance your skills further!

Rashid Ali

0 34 3 minutes read