SQL Server – Second Highest Salary Puzzle – Different Methods & the Best Method for Performance

Friends, today I am going to talk about one of the old things. Different methods to find out 2nd highest salary from the employee salary table. I get this question most of the time while facing technical interviews. Although most of us know solution to this problem but most of us didn’t know the best method to achieve it. In this post I am going to talk about the best method in terms of the performance. So let’s first create a sample table and insert some rows into it.

 ```-- CREATE TABLE EmployeeMaxSalary ( EmpId INT PRIMARY KEY ,EmpName VARCHAR(1) ,EmpSalary INT ) GO INSERT INTO EmployeeMaxSalary SELECT DISTINCT Number , CHAR(Number) , Number * 10 / 2 FROM MASTER..SPT_VALUES WHERE number > 0 AND number < 100 CREATE NONCLUSTERED INDEX Ix_EmpSalary ON EmployeeMaxSalary(EmpSalary) INCLUDE ( EmpName ) -- ```

METHOD 1 | Using Aggregate Function

 ```-- --METHOD 1 | Using Aggregate Function SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary WHERE EmpSalary = ( SELECT Max(EmpSalary) FROM EmployeeMaxSalary WHERE EmpSalary < ( SELECT Max(EmpSalary) FROM EmployeeMaxSalary ) ) -- ```

METHOD 2 | Using TOP WITH TIES

 ```-- --METHOD 2 | Using TOP WITH TIES SELECT TOP 1 WITH TIES EmpId , EmpName , EmpSalary FROM ( SELECT TOP 2 WITH TIES EmpId , EmpSalary ,EmpName FROM EmployeeMaxSalary ORDER BY EmpSalary DESC ) a ORDER BY EmpSalary -- ```

METHOD 3 | Using Ranking Functions

 ```-- --METHOD 3 | Using Ranking Functions SELECT EmpId, EmpName, EmpSalary FROM ( SELECT EmpId, EmpName, EmpSalary , ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) rnk from EmployeeMaxSalary ) X WHERE rnk = 2 -- ```

METHOD 4 | Using Correlated Query

 ```-- --METHOD 4 | Using Correlated Query SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary E1 WHERE 1 = ( SELECT DISTINCT COUNT(*) FROM EmployeeMaxSalary E2 WHERE E2.EmpSalary > E1.EmpSalary ) -- ```

METHOD 5 | Using OffSet (SQL 2012 Onwards)

 ```-- --METHOD 5 | Using OffSet SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary ORDER BY EmpSalary DESC OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY; -- ```

Execution Plan Analysis for all the methods given above-

Pawan Kumar Khowal – Execution Plan Analysis for 2nd Highest Salary Problem

Now I know that my stats are updated so we can trust the cost coming out in the execution plans. Clearly METHOD 5 – Using OffSet & METHOD 3 – Using Ranking Function are the winners. My personal favorite is method 5. Please note that method 5 is available from SQL 2012 onwards;

That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with some more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com