Tuesday, September 4, 2012

Find the 2nd,3rd and 4th Highest Salary in SQL Server


-- 2nd Highest Salary
select max(emp_salary) as [2nd Highest Salary]
      from tblSalary
      where emp_salary
      not in (select top 1 emp_salary
                     from tblSalary
                     GROUP BY emp_salary
                     order by emp_salary desc)
                    
-- 3rd Highest Salary
select max(emp_salary) as [3rd Highest Salary]
      from tblSalary
      where emp_salary
      not in (select top 2 emp_salary
                     from tblSalary
                     GROUP BY emp_salary
                     order by emp_salary desc)
                    
-- 4th Highest Salary
select max(emp_salary) as [4th Highest Salary]
      from tblSalary
      where emp_salary
      not in (select top 3 emp_salary
                     from tblSalary
                     GROUP BY emp_salary
                     order by emp_salary desc)
                    
Notes:

1. emp_salary is the name of column from tblSalary table
2. tblSalary is the name of table
3. Similarly we find the Nth Highest Salary from SQL Server above examle.

No comments: