Find the 3th,4th ,5th........... largest salary

[sonia.sardana]

I know how how to retrive the largest & second largest salary.
tell me how to retrive the 3,4,5,......... ......Largest salary.

Create table empl(empid int, empname varchar,salary int)
insert into empl values(1,'A',10 0)
insert into empl values(2,'B',20 0)
insert into empl values(3,'C',30 0)
insert into empl values(4,'D',40 0)
insert into empl values(5,'E',50 0)
insert into empl values(6,'F',60 0)

Foll. Query will retrive the 2nd largest salary-
select top 1 * from empl where salary<(select MAX(salary)
from empl)order by salary desc

[siva538]

I know how how to retrive the largest & second largest salary.
tell me how to retrive the 3,4,5,......... ......Largest salary.

Create table empl(empid int, empname varchar,salary int)
insert into empl values(1,'A',10 0)
insert into empl values(2,'B',20 0)
insert into empl values(3,'C',30 0)
insert into empl values(4,'D',40 0)
insert into empl values(5,'E',50 0)
insert into empl values(6,'F',60 0)

Foll. Query will retrive the 2nd largest salary-
select top 1 * from empl where salary<(select MAX(salary)
from empl)order by salary desc

select top 1 * from  
(select top 4 * FROM empl 
 order by salary desc) e
order by salary

This is for 4th highest salary. you can replace 4 with any number you want by the number for that much highest salary

[ganeshkumar08]

select e1.sal, e1.name from emp e1
where (N-1) = (select count(distinct e2.sal) from emp e2 where e1.sal > e2.sal
from emp e2)

Where N=Nth highest salary

[deepuv04]

I know how how to retrive the largest & second largest salary.
tell me how to retrive the 3,4,5,......... ......Largest salary.

Create table empl(empid int, empname varchar,salary int)
insert into empl values(1,'A',10 0)
insert into empl values(2,'B',20 0)
insert into empl values(3,'C',30 0)
insert into empl values(4,'D',40 0)
insert into empl values(5,'E',50 0)
insert into empl values(6,'F',60 0)

Foll. Query will retrive the 2nd largest salary-
select top 1 * from empl where salary<(select MAX(salary)
from empl)order by salary desc

Hi,
another way of getting nth record is. first give ranking to each row and get the
record you want
ex:
[code=sql]
SELECT *
FROM
( select DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank, * from empl ) AS E1
WHERE [Rank] IN (3,4,5)
[/code]

DENSE_RANK() is the gives ranking for the rows

thanks

[deveshakgec]

Approach 1 using DENSE_RANK()


Consider following details
table: Emp
Column: name, Salary

(SELECT name , salary , DENSE_RANK() over (order by salary desc) AS Rowno FROM emp) a 
Where a.Rowno=3

Above example is for getting third largest salary.


Appraoch 2: using subquery

SELECT MIN(Salary) from EMP where salary in 
(SELECT TOP 3 salary from emp e order by salary desc)