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

0 votes
asked Dec 11, 2008 by yogini

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

7 Answers

0 votes
answered Dec 11, 2008 by recursive

These work in SQL Server 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.

0 votes
answered Dec 11, 2008 by jayden

In SQL Server 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
0 votes
answered Dec 17, 2009 by yagnesh84

You can find it by 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
0 votes
answered Dec 3, 2010 by bell

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
0 votes
answered Dec 23, 2010 by nilesh
select * from employee2 e
where 2=(select count(distinct salary) from employee2
         where e.salary<=salary)

its working

0 votes
answered Dec 9, 2011 by ritesh-kumar

To find the 5th higest salary from a database, the query is..

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

its working check it out

0 votes
answered Dec 30, 2011 by user683393
SELECT MIN(Salary) FROM (
    SELECT TOP 2 Salary FROM empa ORDER BY Salary DESC
) AS TopFive

It's working correctly, please use it.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...