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

 Posted by Manicse on 11/2/2016 | Category: Sql Server Interview questions | Views: 194 | Points: 40
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;


| Alert Moderator 

Comments or Responses

Login to post response