455,379 Members | 1,404 Online
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
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 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 Apr 19 '08 #2

 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

 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 SELECT * FROM     ( select DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank, * from empl ) AS E1 WHERE [Rank] IN (3,4,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 (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 Expand|Select|Wrap|Line Numbers SELECT MIN(Salary) from EMP where salary in  (SELECT TOP 3 salary from emp e order by salary desc) Nov 13 '13 #5