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

Question

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

Refer

Answer 1

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

Shaunda

Answer 2

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.

Barmecide

I want it in a single query using percent... how to get that? select top 5 percent columnname from tablename order by desc using this we get first 5 records, but I want only 5th one. – Refer

Both queries return a single number which is the 5th highest salary. Have you tried them? – Barmecide

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

Nomen

Answer 4

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

Canale

Answer 5

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

It's working correctly, please use it.

Nightgown

Answer 6

Can be Most easily solved by:

SELECT MIN(Salary) FROM (
SELECT Salary FROM empa ORDER BY Salary DESC limit 5
)TopFive

Spoof

Answer 7

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

Ayah

This approach will work, but has poor performance. It is O(n^2), since for every employee in the outer query, the entire table must be scanned again. – Barmecide

Answer 8

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

Korte

Answer 9

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

Gaylene

Answer 10

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

its working

Ventage

Answer 11

You can use the following one, it will help

SELECT MIN (Salary) AS Fifth_Highest FROM Employees WHERE Salary IN (SELECT DISTINCT TOP 5 Salary FROM Employees ORDER BY Salary DESC)

Paschasia

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Cari