SQL query to find Nth highest salary from a salary table ?

CREATE TABLE Salaries (
  EmployeeID int not null ,
  Salary decimal(18,2) not null
  )

INSERT INTO Salaries SELECT 10101 , 50000
INSERT INTO Salaries SELECT 90140 , 35000
INSERT INTO Salaries SELECT 90151 , 72000
INSERT INTO Salaries SELECT 18010 , 39000
INSERT INTO Salaries SELECT 92389 , 80000

DECLARE @N int
SET @N = 3  -- Change the value here to pick a different salary rank

SELECT Salary
FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N


or

SELECT * FROM EMPLOYEE e1
WHERE N-1 = 
           (SELECT COUNT(e2.ORIG_SALARY) FROM EMPLOYEE e2
                  WHERE e2.ORIG_SALARY > e1.ORIG_SALARY)

About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments