Write a SQL query to find the 5th highest employee salary from an Employee table.

Answer:

SELECT TOP (1) Salary FROM

(

    SELECT DISTINCT TOP (5) Salary FROM Employee ORDER BY Salary DESC

) AS Emp ORDER BY Salary

First, the SELECT DISTINCT TOP (5) Salary FROM Employee ORDER BY Salary DESC query will select the top 5 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 5th highest salary.

Therefore, the second query reorders the 5 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 5 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 5

) AS Emp ORDER BY Salary LIMIT 1;