Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

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

Advertisements