Articles
Second Highest Salary in SQL: Four Working Queries and Which to Lead With
Compare four verified PostgreSQL solutions for second highest salary, then handle ties, NULLs, departments, and salary history grain.
“Second highest salary” is a question about the second highest distinct non-NULL salary value. That definition matters when the top salary is tied, a salary is missing, or a department has only one salary level.
There are four useful PostgreSQL solutions. The compact MAX exclusion is a good lead when the output is one value. DENSE_RANK is easier to extend when the next question asks for the Nth value, the employees at that value, or one result per department.
Define the result contract first
Before choosing syntax, settle three points:
| Decision | Contract used here | Why it changes the query |
|---|---|---|
| Are ties distinct positions? | No. Equal salaries share one level. | Ranking rows would return another top earner instead of the next salary value. |
| What does NULL mean? | Unknown salary, excluded from ranking. | PostgreSQL can place NULL first in a descending sort unless it is handled explicitly. |
| What if no second level exists? | Return one row containing NULL for a value-only result. | A filtered row query can otherwise return no row. |
The fixture below deliberately contains a tied maximum, a tied salary inside Sales, a one-level Support department, and a NULL salary. Those rows make the differences observable.
MAX exclusion and DISTINCT with OFFSET
The MAX solution removes every row at the highest salary value, then takes the maximum of what remains. Both MAX calls ignore NULL.
Input: employees
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Ada | Engineering | 195000 |
| 2 | Bruno | Engineering | 195000 |
| 3 | Chen | Engineering | 160000 |
| 4 | Dara | Sales | 140000 |
| 5 | Eli | Sales | 120000 |
| 6 | Fay | Sales | 120000 |
| 7 | Gus | Support | 90000 |
| 8 | Nia | Support | NULL |
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
Output
| second_highest |
|---|
| 160000 |
If no lower salary exists, the outer aggregate still emits one row and its value is NULL. This form is concise, though it becomes awkward when the output must include employee columns.
DISTINCT plus OFFSET expresses the same value ladder directly. The scalar subquery converts a missing second row into a NULL value.
Input: employees
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Ada | Engineering | 195000 |
| 2 | Bruno | Engineering | 195000 |
| 3 | Chen | Engineering | 160000 |
| 4 | Dara | Sales | 140000 |
| 5 | Eli | Sales | 120000 |
| 6 | Fay | Sales | 120000 |
| 7 | Gus | Support | 90000 |
| 8 | Nia | Support | NULL |
SELECT (
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1
) AS second_highest;
Output
| second_highest |
|---|
| 160000 |
The SQL order-of-operations guide explains why deduplication precedes the final sort and limit.
DENSE_RANK and correlated distinct counting
DENSE_RANK gives equal salaries the same position and advances the next distinct salary by one. Filtering in an outer query block is required because the window result does not exist at the inner WHERE stage.
Input: employees
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Ada | Engineering | 195000 |
| 2 | Bruno | Engineering | 195000 |
| 3 | Chen | Engineering | 160000 |
| 4 | Dara | Sales | 140000 |
| 5 | Eli | Sales | 120000 |
| 6 | Fay | Sales | 120000 |
| 7 | Gus | Support | 90000 |
| 8 | Nia | Support | NULL |
WITH ranked AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT MAX(salary) AS second_highest
FROM ranked
WHERE salary_rank = 2;
Output
| second_highest |
|---|
| 160000 |
Change the filter to another rank for an Nth-highest value. If the output should show people, select employee columns from the ranked CTE instead of collapsing with MAX. The window-functions guide covers ranking ties in depth.
The fourth solution counts how many distinct salary values are greater than each employee's salary. A salary is second highest when exactly one distinct value exceeds it.
Input: employees
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Ada | Engineering | 195000 |
| 2 | Bruno | Engineering | 195000 |
| 3 | Chen | Engineering | 160000 |
| 4 | Dara | Sales | 140000 |
| 5 | Eli | Sales | 120000 |
| 6 | Fay | Sales | 120000 |
| 7 | Gus | Support | 90000 |
| 8 | Nia | Support | NULL |
SELECT MAX(e.salary) AS second_highest
FROM employees AS e
WHERE e.salary IS NOT NULL
AND (
SELECT COUNT(DISTINCT e2.salary)
FROM employees AS e2
WHERE e2.salary > e.salary
) = 1;
Output
| second_highest |
|---|
| 160000 |
This form explains the ranking definition without a window, but it repeats the higher-value comparison for outer rows. Choose it for reasoning clarity when that tradeoff fits the dataset and engine; do not assume it is faster or slower without inspecting the actual plan. The SQL COUNT guide explains the distinct-count semantics used here.
Second highest within each department
Add a partition when the value ladder restarts by department. Aggregating the ranked rows instead of filtering them away keeps Support in the output with a NULL second salary.
Input: employees
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Ada | Engineering | 195000 |
| 2 | Bruno | Engineering | 195000 |
| 3 | Chen | Engineering | 160000 |
| 4 | Dara | Sales | 140000 |
| 5 | Eli | Sales | 120000 |
| 6 | Fay | Sales | 120000 |
| 7 | Gus | Support | 90000 |
| 8 | Nia | Support | NULL |
WITH ranked AS (
SELECT
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
)
SELECT
department,
MAX(salary) FILTER (WHERE salary_rank = 2) AS second_highest
FROM ranked
GROUP BY department
ORDER BY department;
Output
| department | second_highest |
|---|---|
| Engineering | 160000 |
| Sales | 120000 |
| Support | NULL |
If the requested output is employees rather than salary values, filter salary_rank = 2 and return every matching employee. Sales would then return both Eli and Fay. If exactly one employee is required, use ROW_NUMBER with a documented deterministic tie-breaker instead of pretending the tie does not exist.
Rank current records, not history rows
A salary history table is at employee-change grain, not employee grain. Reduce it to one current row per employee before ranking. The history ID breaks a same-date tie deterministically.
Input: salary_history
| history_id | employee_id | employee_name | salary | effective_date |
|---|---|---|---|---|
| 1 | 1 | Ada | 175000 | 2025-01-01 |
| 2 | 1 | Ada | 195000 | 2026-06-01 |
| 3 | 2 | Bruno | 195000 | 2026-03-01 |
| 4 | 3 | Chen | 160000 | 2025-02-01 |
| 5 | 3 | Chen | 165000 | 2026-07-01 |
WITH current_pay AS (
SELECT employee_id, employee_name, salary
FROM (
SELECT
history_id,
employee_id,
employee_name,
salary,
ROW_NUMBER() OVER (
PARTITION BY employee_id
ORDER BY effective_date DESC, history_id DESC
) AS row_num
FROM salary_history
) AS versioned
WHERE row_num = 1
),
ranked AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM current_pay
WHERE salary IS NOT NULL
)
SELECT MAX(salary) AS second_highest_current_salary
FROM ranked
WHERE salary_rank = 2;
Output
| second_highest_current_salary |
|---|
| 165000 |
The key lesson is grain order: resolve the current employee state first, then rank that state. More grain-first exercises are available in SQL practice questions.
FAQ
Why is RANK not used for the second distinct salary?
RANK leaves gaps after ties. Two employees tied at the top both receive rank 1, so the next salary receives rank 3. DENSE_RANK gives the next distinct value rank 2.
Why not use ROW_NUMBER?
ROW_NUMBER assigns a different position to every row. With a tied top salary, row 2 is another top earner. Use it when the requirement is exactly one row and you have a meaningful tie-breaker, not when ranking distinct salary values.
What if salaries can be NULL?
Exclude them explicitly before a descending window or offset sort. MAX already ignores NULL, but an explicit policy still makes the business meaning clear.
Which solution should I lead with?
Use MAX exclusion for one scalar second-highest value because the contract is easy to explain. Use DENSE_RANK when the question is likely to expand to Nth highest, employee details, or per-group results.
How do I return NULL when no second value exists?
An ungrouped aggregate such as MAX returns one row containing NULL when no row qualifies. For the offset approach, wrap the limited query as a scalar subquery, as shown above.