SQL · easy

Write a query to find the second highest salary from an Employees table.

Asked in Data Analyst interviews, in the SQL round.

Short answer

Use a subquery with MAX where salary < (SELECT MAX(salary)), or DENSE_RANK() = 2.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

employees

How to answer it

Definition

Return the second largest DISTINCT salary. The word that matters is distinct: if two people share the top salary, the second highest is the next value down, not the second row.

Two answers

The subquery version reads like the sentence: the largest salary smaller than the largest salary.

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

The window version generalises to Nth and is the one that shows you know window functions.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM   employees
) ranked
WHERE rnk = 2;

Output

second_highest
--------------
        168000

Why DENSE_RANK and not the others

salary   ROW_NUMBER   RANK   DENSE_RANK
168000            1      1            1
168000            2      1            1
151000            3      3            2

ROW_NUMBER would call the second 168000 the runner-up, which is wrong. RANK skips 2 entirely and returns nothing. DENSE_RANK gives the second distinct value, which is what the question asked for.

Key points

• Say "second distinct salary" out loud. It is the whole question.
• LIMIT 1 OFFSET 1 works only when salaries are unique, and silently breaks the moment they are not.
• Both queries return NULL or no row when every salary is the same. Say so before being asked.
• The window version answers "third highest" by changing one number; the subquery version does not.

FAQ

• Which is faster? The subquery, usually, since it is two cheap aggregate scans. The window version sorts.
• What about NULL salaries? MAX ignores them, and so does the ordering. Mention it if the column is nullable.
• Can I use LIMIT? Only with SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET 1, which is correct but reads as a trick.

The 30-second answer

The subtlety is that they want the second distinct salary, not the second row, so ties have to collapse. I would use DENSE_RANK ordered by salary descending and take rank two, because DENSE_RANK is the one that treats tied salaries as a single rank. ROW_NUMBER would call a tied top earner the runner-up, and RANK would skip rank two altogether and return nothing.

The quick version is MAX of salary where salary is less than the overall MAX, which reads well but only answers second, not Nth. Either way I would say what happens when every salary is identical: you get no row back.

In the interview

• What they are checking: whether you reach for a window function, and whether you know what happens on ties.
• Common mistake: ORDER BY salary DESC LIMIT 1 OFFSET 1. It works on unique salaries and is wrong the moment two people earn the same amount.

Practice this for real

• Check your resume against a Data Analyst job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card