SQL Exercises: Display employees who get the second highest salary

Last update on March 12 2025 12:11:11 (UTC/GMT +8 hours)

From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.

Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 2006-02-05 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12008.00 |           0.00 |        101 |           100 |
|         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100 |
|         110 | John        | Chen        | JCHEN    | 515.124.4269       | 2005-09-28 | FI_ACCOUNT |  8200.00 |           0.00 |        108 |           100 |
....................
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample Solution:

-- Selecting all columns (*) from the 'employees' table
SELECT * 

-- Filtering rows based on the condition that the 'employee_id' is in the result set of a nested subquery
FROM employees 

-- Subquery to find 'employee_id' values where the 'salary' matches the maximum salary below the overall maximum salary
WHERE employee_id IN 

  -- Subquery to find 'employee_id' values where the 'salary' matches the maximum salary below the overall maximum salary
  (SELECT employee_id 
   FROM employees  

   -- Subquery to find the maximum salary below the overall maximum salary in the 'employees' table
   WHERE salary = 
     (SELECT MAX(salary) 
      FROM employees 
      WHERE salary < 
        (SELECT MAX(salary) FROM employees)
     )
  );

Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
101		Neena		Kochhar		NKOCHHAR515.123.4568	2005-09-21	AD_VP	17000.00	0.00	100		90
102		Lex		De Haan		LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	0.00	100		90

Code Explanation:

The said query in SQL that retrieves all columns (denoted by *) from the employees table where the employee ID matches the employee ID of the second highest salary in the employees table. The first subquery in the IN clause calculates the maximum salary in the employees table, and the second subquery calculates the second highest salary by finding the maximum salary in the employees table while excluding the highest salary.
The result of the second subquery is then used to find the employee ID(s) of the second highest salary in the main query by matching the salary.
The final result of the query will be all the employees who have the second highest salary in the employees table.

Visual Presentation:

Alternative Statements:

Code - 1:

SELECT * 
FROM employees 
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees 
    WHERE salary < (
        SELECT MAX(salary) 
        FROM employees
    )
);

Code - 2:

SELECT * 
FROM employees 
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Display employees not working in certain departments.
Next SQL Exercise: Employee names and hire dates for Claras department.