Back to questions
Imagine you're an HR analyst at a tech company tasked with analyzing employee salaries. Your manager is keen on understanding the pay distribution and asks you to determine the second highest salary among all employees.
It's possible that multiple employees may share the same second highest salary. In case of duplicate, display the salary only once.
column_name | type | description |
---|---|---|
employee_id | integer | The unique ID of the employee. |
name | string | The name of the employee. |
salary | integer | The salary of the employee. |
department_id | integer | The department ID of the employee. |
manager_id | integer | The manager ID of the employee. |
employee_id | name | salary | department_id | manager_id |
---|---|---|---|---|
1 | Emma Thompson | 3800 | 1 | 6 |
2 | Daniel Rodriguez | 2230 | 1 | 7 |
3 | Olivia Smith | 2000 | 1 | 8 |
second_highest_salary |
---|
2230 |
The output represents the second highest salary among all employees. In this case, the second highest salary is $2,230.
The dataset you are querying against may have different input & output - this is just an example!
First, we determine the highest salary from the table using aggregate function. Why? We need to know the highest salary to identify the second highest salary. Learn about the function here.
Subsequently, we use the result of the query in Step 1 as a subquery. It selects the maximum salary from the table where the salary is less than the highest salary obtained in the subquery. By comparing salaries to the maximum salary from Step 1, we can exclude the highest salary itself and identify the second highest salary.
Alternatively, you can utilize a CTE instead of a subquery.
PostgreSQL 14