Find the second (or Nth) highest salary, overall and per department.

The most general answer uses a window function: rank the distinct salary values in descending order with DENSE_RANK() and keep rank N. It handles ties correctly, extends to “per department” with PARTITION BY, and works for any N. Before writing it, I would ask: if two people share the top salary, is the “second highest” the next distinct value or the second row? That decides which ranking function to use.

Window function (PostgreSQL, SQL Server, Oracle, MySQL 8+)

-- All employees earning the 2nd highest distinct salary
SELECT id, name, salary
FROM (
  SELECT id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2;

RANK vs DENSE_RANK vs ROW_NUMBER

For salaries 100, 100, 90, 80:

salary	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
80	4	4	3

• DENSE_RANK = 2 gives 90: the second highest distinct value. Usually what is meant.
• RANK = 2 returns nothing, because of the gap after the tie.
• ROW_NUMBER = 2 returns one of the 100 rows arbitrarily unless you add a tie-breaker.

Per department

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2
ORDER BY department_id;

Departments with only one distinct salary produce no row. If the requirement is “show NULL for those departments”, left-join this result to the departments table.

Simpler alternatives

Second highest with a subquery (portable, but only works for N = 2 without nesting):

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

This returns a single row with NULL if there is no second value, which is often the desired behaviour in the classic interview version of the question.

Nth highest with LIMIT/OFFSET (PostgreSQL, MySQL):

SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;   -- OFFSET N-1

Without DISTINCT, ties break it. The standard SQL spelling is OFFSET 1 ROWS FETCH FIRST 1 ROW ONLY; SQL Server supports only this form (it has no LIMIT) and requires an ORDER BY with it.

NULL handling

In PostgreSQL, NULLs sort first in descending order by default, so a NULL salary would be ranked 1 unless you filter it or add NULLS LAST. MySQL and SQL Server sort NULLs last in descending order. This is a nice detail to raise because it is exactly the kind of difference that causes test-data surprises across databases.

What interviewers look for

• A correct window-function solution and the ability to explain it.
• Clarifying ties, and choosing DENSE_RANK deliberately.
• PARTITION BY for the per-group variant.
• Edge cases: fewer than N values, NULL ordering differences between databases.