mediumCodingConceptEntryMidSenior
Write a query returning the second-highest salary in each department, then tell me what it does when two people tie for the top.
To get the second highest salary per department in SQL, rank employees inside each department with DENSE_RANK and filter rank two. DENSE_RANK handles ties where ROW_NUMBER or a global MAX gives the wrong result. Use this window functions answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects sql queries to the point an interviewer is testing.
3 min readUpdated 2026-08-15
Answer
Short answer
Use DENSE_RANK() over partition by department order by salary desc, then keep rank = 2. That returns the second distinct salary per department and handles tied top salaries correctly.
Was this answer helpful?
Likely follow-ups
- Rewrite it without window functions, using only a correlated subquery or a self-join.
- Now return the Nth highest for a parameterised N. What changes?
- How would you list departments that have no second-highest salary at all?
- Given a table of invoice numbers with holes in it, find every missing range.
Related questions
- Adding a second LEFT JOIN doubled the revenue figure on a report. Explain what happened and write the correct query.mediumAlso on sql-queries and null-handling4 min
- Your Collectors.toMap call passed every test and threw in production. What did the code assume, and what are your options?mediumAlso on null-handling4 min
- You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?mediumAlso on sql-queries6 min
- Why is a recommender built as candidate generation followed by ranking rather than as one model?hardAlso on ranking5 min