How to find the fifth highest salary in a single query in SQL Server

Question

How to find the fifth highest salary in a single query in SQL Server

Answer 1

In SQL Server 2005 and 2008, create a ranked subquery query, then add a where clause, where rank = 5.

select * from ( Select SalesOrderID, CustomerID, Row_Number() Over (Order By SalesOrderID) as RunningCount From Sales.SalesOrderHeader Where SalesOrderID > 10000 Order By SalesOrderID ) ranked where RunningCount = 5 

Answer 2

They work in SQL Server 2000

 DECLARE @result int SELECT TOP 5 @result = Salary FROM Employees ORDER BY Salary DESC 

The syntax should be close. I can not verify this at the moment.

Or you can go with a subquery:

 SELECT MIN(Salary) FROM ( SELECT TOP 5 Salary FROM Employees ORDER BY Salary DESC ) AS TopFive 

Again, not positively if the syntax is exactly right, but the approach works.

Answer 3

 SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary where n > 1 -- (n is always greater than one) 

You can find any number of the highest salaries using this query.

Answer 4

To find the 5th highest salary from the database, the query will be ...

 select MIN(esal) from ( select top 5 esal from tbemp order by esal desc) as sal 

his working check

Answer 5

 SELECT MIN(Salary) FROM ( SELECT TOP 2 Salary FROM empa ORDER BY Salary DESC ) AS TopFive 

It works correctly, please use it.

Answer 6

You can try something like:

 select salary from Employees a where 5=(select count(distinct salary) from Employees b where a.salary > b.salary) order by salary desc 

Answer 7

You can find it using this query:

 select top 1 salary from (select top 5 salary from tbl_Employee order by salary desc) as tbl order by salary asc 

Answer 8

The request below receives the highest salary after a specific employee name.

Just learn it!

 SELECT TOP 1 salary FROM ( SELECT DISTINCT min(salary) salary FROM emp where salary > (select salary from emp where empname = 'John Hell') ) a ORDER BY salary 

Answer 9

 select * from employee2 e where 2=(select count(distinct salary) from employee2 where e.salary<=salary) 

his working