How to find fifth highest salary in a single query in MS Sql Server

Question

How to find fifth highest salary in a single query in MS Sql Server

Answer 1

In SQL 2005 & 2008, create a ranked subselect query, then add a where clause where the 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

These work in MSSQL 2000

DECLARE @result int

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

Syntax should be close. I can't test it at the moment.

Or you could go with a subquery:

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

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

Answer 3

You can try some thing 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 4

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 highest salary from this query

Answer 5

you can find by this query

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

Answer 6

Hi, The below query to gets the Highest salary after particular Employee name.

Just have a look into that!

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

Regards,

Bell

Answer 7

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

its working