Write a SQL query to find the 10th highest employee salary from an
Employee table. Explain your answer.
(Note: You may assume that there are at least 10 records in the
This can be done as follows:
SELECT TOP (1) Salary FROM ( SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary
This works as follows:
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 10th highest salary.
Therefore, the second query reorders the 10 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 10 salaries).
Not all databases support the
TOP keyword. For example, MySQL and PostreSQL use the
LIMIT keyword, as follows:
SELECT Salary FROM ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10 ) AS Emp ORDER BY Salary LIMIT 1;