By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,404 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

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

P: 95
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',100)
insert into empl values(2,'B',200)
insert into empl values(3,'C',300)
insert into empl values(4,'D',400)
insert into empl values(5,'E',500)
insert into empl values(6,'F',600)

Foll. Query will retrive the 2nd largest salary-
select top 1 * from empl where salary<(select MAX(salary)
from empl)order by salary desc
Apr 19 '08 #1
Share this Question
Share on Google+
4 Replies


P: 44
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',100)
insert into empl values(2,'B',200)
insert into empl values(3,'C',300)
insert into empl values(4,'D',400)
insert into empl values(5,'E',500)
insert into empl values(6,'F',600)

Foll. Query will retrive the 2nd largest salary-
select top 1 * from empl where salary<(select MAX(salary)
from empl)order by salary desc
Expand|Select|Wrap|Line Numbers
  1. select top 1 * from  
  2. (select top 4 * FROM empl 
  3.  order by salary desc) e
  4. order by salary 
  5.  
This is for 4th highest salary. you can replace 4 with any number you want by the number for that much highest salary
Apr 19 '08 #2

ganeshkumar08
P: 31
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
Apr 21 '08 #3

deepuv04
Expert 100+
P: 227
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',100)
insert into empl values(2,'B',200)
insert into empl values(3,'C',300)
insert into empl values(4,'D',400)
insert into empl values(5,'E',500)
insert into empl values(6,'F',600)

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM
  3.     ( select DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank, * from empl ) AS E1
  4. WHERE [Rank] IN (3,4,5)    
  5.  
DENSE_RANK() is the gives ranking for the rows

thanks
Apr 21 '08 #4

P: 5
Approach 1 using DENSE_RANK()


Consider following details
table: Emp
Column: name, Salary


Expand|Select|Wrap|Line Numbers
  1. (SELECT name , salary , DENSE_RANK() over (order by salary desc) AS Rowno FROM emp) a 
  2. Where a.Rowno=3
Above example is for getting third largest salary.


Appraoch 2: using subquery


Expand|Select|Wrap|Line Numbers
  1. SELECT MIN(Salary) from EMP where salary in 
  2. (SELECT TOP 3 salary from emp e order by salary desc)
Nov 13 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.