Find 5th,3th and 2th highest salary

Posted by Alok under Sql Server on 1/16/2013 | Points: 10 | Views : 2828 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

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

Latest Posts