Social Icons

Friday, April 13, 2012

How to find the nth Highest Salary from Employee Table

You can find out the nth Highest Salary from the Employee Table by using following systax

select top 1 * from testEmp
where Emp_Id not in
(select top (n-1) Emp_Id from testEmp order by salary desc)
order by salary desc

Let Suppose following is you table

Emp_Id
Employee_Name
Salary
1602
Khalid Rafique
40000
1650
Jamal Ali Khan
45000
1530
Farhan Khan
29000
1533
Abid sultan
33000
1535
Atiq Khan
38000
1730
Rashid Ali
21000

if you want to find 2nd highest salary, you need to have to write the following query

select top 1 * from testEmp 
where Emp_Id not in
(select top (2-1) Emp_Id from testEmp order by salary desc)
order by salary desc

Result:
Emp_Id
Employee_Name
Salary
1602
Khalid Rafique
40000


if you want to find 3rd highest salary, you need to have to write the following query
select top 1 * from testEmp 
where Emp_Id not in
(select top (3-1) Emp_Id from testEmp order by salary desc)
order by salary desc


Result:
Emp_Id
Employee_Name
Salary
1535
Atiq Khan
38000

if you want to find 4rd highest salary, you need to have to write the following query

select top 1 * from testEmp 
where Emp_Id not in
(select top (4-1) Emp_Id from testEmp order by salary desc)
order by salary desc


Result:
Emp_Id
Employee_Name
Salary
1533
Abid sultan
33000

No comments:

Post a Comment

 

Sample text

Sample Text