Salary classics

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

"Find the second highest salary." Every SQL round starts here, and most candidates lose marks on it, not because they cannot write it but because they never ask: what about ties, and what if there is no second salary?

You'll learn to

  • Ask the three edge-case questions before writing: ties, NULLs, empty result
  • Solve second and Nth highest salary so they return NULL when there is none
  • Use self-joins for employee and manager problems
  • Find duplicates and per-group maximums with ties

Learn

How to attack any SQL interview problem

Before you type, say these out loud. Interviewers grade them.

  1. What exactly comes back? Which columns, one row or many, sorted how?
  2. Ties: if two people share the top salary, are both "first"? Is the next one second or third?
  3. NULLs: an employee with no department, a CEO with no manager, a missing email.
  4. Empty: if there is no answer, do we return no row or one row with NULL?

Then write the simplest query, and test it on a tie, a NULL and an empty table in your head.

Second highest salary, three ways

-- 1. MAX below the MAX: one row, NULL when there is no second salary
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. DENSE_RANK: ties share a rank, so rank 2 is the second distinct salary
SELECT MAX(salary) AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) AS t
WHERE r = 2;

-- 3. OFFSET: needs DISTINCT, and returns NO ROW when there is no second salary
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

All three print 260000 here. Change the data with UPDATE employees SET salary = 100000; at the top and run again: the first two give one row with NULL, the third gives no row at all. Wrapping it as SELECT (subquery) AS second_highest turns "no row" into NULL.

Employee next to manager: a self-join

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;

The same table appears twice with two aliases: e for the employee, m for the manager. The join key is the employee's manager_id = the manager's emp_id. Priya earns ₹2,40,000 and her manager Sana ₹2,30,000.

LIMIT 1 OFFSET 1 without DISTINCT gives the second row, not the second salary. If two people share the top salary, it returns the top salary again. And it returns zero rows when the answer should be NULL.

MySQL also has LIMIT 1 OFFSET 1, and the short form LIMIT 1, 1 (offset first, then count). Window functions like DENSE_RANK need MySQL 8 or later; older versions use the MAX-below-MAX trick.

In one line

Second/Nth highest: MAX below MAX or DENSE_RANK, wrapped so "none" is one NULL row; manager problems are self-joins on e.manager_id = m.emp_id.

Interview corner

Find the second highest salary. What edge cases do you check?

Ties at the top (use distinct salaries), fewer than two distinct salaries (return NULL, not an empty result), and NULL salaries. SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); handles all three; for the Nth, use DENSE_RANK() and WHERE r = N inside MAX(...).

RANK, DENSE_RANK or ROW_NUMBER for "Nth highest salary"?

DENSE_RANK. It gives equal salaries the same rank and does not skip numbers, so rank N is the Nth distinct salary. RANK skips after a tie (1, 1, 3), and ROW_NUMBER gives tied rows different numbers.

Why does `NOT IN` sometimes return nothing?

If the subquery returns a NULL, x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL, and the last part is unknown, so the whole condition is never true. Use NOT EXISTS, or filter NULLs out of the subquery.