What SQL query finds the third largest salary in an employee table?
This problem tests the candidate's ability to write complex SQL queries involving sorting, limiting, and offsetting.
Why Interviewers Ask This
Recruiters use this question to assess logical thinking and proficiency in database querying, specifically regarding ranking and filtering data. It checks if the candidate understands how to handle duplicates and order data correctly to find specific rank values. This is a common scenario in reporting and analytics where finding top N records is a frequent requirement.
How to Answer This Question
Explain the logic of ordering salaries in descending order to bring the highest values to the top. Discuss the use of LIMIT and OFFSET clauses to skip the top two entries and retrieve the third. Mention handling duplicates using DISTINCT if necessary to ensure accurate ranking. Alternatively, suggest using window functions like DENSE_RANK() for more complex scenarios, but focus on the standard SQL approach for clarity.
Key Points to Cover
- Order by salary descending
- Use DISTINCT to handle duplicates
- Apply OFFSET to skip top rows
- Limit result to single row
Sample Answer
The most straightforward approach is to select distinct salaries, order them in descending order, and then use the LIMIT and OFFSET clauses. The query would look like: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2. This sorts all unique salaries from highest to lowest, skips the first two (the highest and second highest), and returns the third one. If duplicates are allowed and count towards rank, we might need a subquery or window function, but for distinct values, this method is efficient and readable.
Common Mistakes to Avoid
- Ignoring duplicate salary values
- Using LIMIT 3 instead of OFFSET 2
- Not specifying ORDER BY direction
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
What is ER model in the DBMS?
Medium
FlipkartWhat is database normalization and why is it important?
Medium
FlipkartWhat is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is the difference between DELETE and TRUNCATE commands in SQL?
Easy
FlipkartWhat is the best way to find the middle of three numbers?
Easy
InfosysWrite Selenium and Java code to automate an Amazon search
Medium
Infosys