Amazon Interview Question
Web DevelopersSELECT * FROM employee_salary WHERE salary = (SELECT salary FROM employee_salary ORDER BY salary DESC LIMIT 7,1);
This will work, tested, OK
select * from employee where salary = (select distinct(salary) from employee order by salary desc limit 6,1)
I know there's wording issues in the question, but the 7th highest salary should refer to 7th highest pay scale.
Lets say Bob A, Bob B, Bob C, Bob D, Bob E, Bob F, Bob G are all at salary cap. Using the queries in other examples, Bob G would be returned as having the 7th highest salary. Even though he's as max salary which would be False. It would also only return Bob G because of where his name lies alphabetically.
This query would return all employees who are at the 7th highest salary as the question asks.
SELECT name
FROM employee_salary
WHERE salary = (
SELECT DISTINCT salary
FROM employee_salary
ORDER BY salary DESC
LIMIT 6 , 1 )
select * from employee_salary order by salary desc limit 6,1
- Anonymous May 29, 2009