Find 5th,3th and 2th highest salary

Posted by Alok under Sql Server on 1/16/2013 | Points: 10 | Views : 1621 | Status : [Member] | Replies : 4
i have table like this

emp_id Salary

A001 1000
A002 2000
A003 3000
A004 4000
A005 5000
A006 6000
A007 7000
A008 8000
A009 9000
A010 10000

i want a query to display first 5th,3th,2th highest salary and then all salary
as given below--
emp_id Salary

A006 6000
A008 8000
A009 9000
A001 1000
A002 2000
A003 3000
A004 4000
A005 5000
A007 7000
A010 10000

Thanks & Regards,
Alokssm



Responses

Posted by: Hariinakoti on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Alok,

See this link it'll helpful to you

http://www.aspdotnet-suresh.com/2011/12/query-to-find-nth-highest-salary-of.html


if u'll get answer from this link please select mark as answer

Thanks & Regards
Hari

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amaren1982 on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
create table Emp (emp_id varchar(20), Salary int)


Insert into Emp (emp_id ,Salary)
values ('A001', 1000),('A002' ,2000),('A003', 3000),('A004', 4000),('A005', 5000),('A006', 6000),
('A007', 7000),
('A008' ,8000),
('A009', 9000),
('A010', 10000)



create table #TempEmp (Positionint int ,emp_id varchar(20), Salary int)

insert into #TempEmp (Positionint ,emp_id , Salary )
select Position ,emp_id ,Salary from (
select ROW_NUMBER() Over (order by Salary desc) Position ,emp_id ,Salary from Emp )ss
where Position in (5,3,2) order by Position desc

insert into #TempEmp (Positionint ,emp_id , Salary )
select Position ,emp_id ,Salary from (
select ROW_NUMBER() Over (order by Salary desc) Position ,emp_id ,Salary from Emp )ss
where Position not in (5,3,2)

select * from #TempEmp

drop table #TempEmp



Warm Regards,
AMARENDRA KUMAR AMAR
SSE
9990662544
amaren1982@gmail.com

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankit Shukla on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
U can get 5th,3rd n 2nd highest salary like this.hope it's working..

select * from
(select ROW_NUMBER() OVER(ORDER BY Salary desc) SrNo, Salary From emp) tbl
where tbl.SrNo in (5,3,2) order by Salary desc.

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prashikudupi on: 1/22/2013 [Member] Starter | Points: 25

Up
0
Down
CREATE TABLE EMP(EmpID VARCHAR(10),Salary INT)

Insert into EMP(EmpID ,Salary) values ('A001', 1000),('A002' ,2000),('A003', 3000),('A004', 4000),('A005', 5000),('A006', 6000),
('A007', 7000),('A008' ,8000),('A009', 9000),('A010', 10000)

SELECT T1.*,ROW_NUMBER() OVER(ORDER BY T1.SALARY ASC) EMP_ORD
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY) IN (2,3,5) )
UNION
SELECT T1.*,3+ROW_NUMBER() OVER(ORDER BY T1.SALARY ASC) EMP_ORD
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY) NOT IN (2,3,5) )
ORDER BY EMP_ORD

See this link for other methods:

http://sqlbay.blogspot.in/2013/01/find-2nd3rd-and-5th-highest-salary-of.html


Alok, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response