Which SQL query finds the third largest salary in an employee table?
A classic database question requiring knowledge of sorting, filtering, and offsetting logic in SQL.
Why Interviewers Ask This
This question assesses a developer's ability to manipulate data efficiently using SQL. It tests understanding of ranking functions, ordering, and limiting result sets. Employers look for candidates who can solve problems involving relative positioning in datasets without relying on complex subqueries if simpler methods exist.
How to Answer This Question
Present the solution using the LIMIT and OFFSET clauses for simplicity, or discuss the DENSE_RANK() window function for more robust handling of ties. Explain why DISTINCT might be needed if duplicate salaries exist. Clarify the logic behind OFFSET 2 (skipping top two) to reach the third. Mention alternative approaches like subqueries if the database doesn't support LIMIT.
Key Points to Cover
- Order salaries in descending order
- Use LIMIT and OFFSET or RANK functions
- Handle duplicate salary values
- Ensure correct syntax for the DBMS
Sample Answer
The most straightforward way is to select distinct salaries, order them in descending order, and skip the top two entries. The query would be: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2.…
Common Mistakes to Avoid
- Using OFFSET 3 instead of OFFSET 2
- Ignoring duplicate salary scenarios
- Sorting in ascending order by mistake
- Forgetting DISTINCT clause
Sound confident on this question in 5 minutes
Answer once and get a 30-second AI critique of your structure, content, and delivery. First attempt is free — no signup needed.