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;