SQL Puzzle | Calculate 2nd Highest & 2nd Lowest Salary / Nth Highest and Nth Lowest Salary at the same time

This question was asked to me in one of the technical interviews I had recently attended. Here we have to find out Nth Highest and Nth Lowest Salary at the same time. So effectively we will have 2 rows.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE testEmp
(
	 ID SMALLINT
	,EmpName VARCHAR(1)
	,Salary INT
)
GO

INSERT INTO testEmp VALUES
(1,	'P',	100 ),
(2,	'Q',	500 ),
(3,	'R',	230 ),
(4,	'S',	1000 ),
(5,	'L',	670 )
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Row Numbers

--

DECLARE @NthHighestLowest AS INT = 2

SELECT ID,EmpName,Salary FROM
(
	SELECT 
		ID,EmpName,Salary
		,ROW_NUMBER() OVER (ORDER BY Salary ASC) r1
		,ROW_NUMBER() OVER (ORDER BY Salary DESC) r2
	FROM testEmp
)x WHERE r1 = @NthHighestLowest OR r2 = @NthHighestLowest
ORDER BY r1


--

SOLUTION 2 | Using Corelated Query

--

SELECT ID,EmpName,Salary FROM testEmp e1
WHERE  (@NthHighestLowest-1) = (SELECT DISTINCT COUNT(*) FROM testEmp e2 WHERE e2.Salary > e1.Salary )
	OR (@NthHighestLowest-1) = (SELECT DISTINCT COUNT(*) FROM testEmp e2 WHERE e2.Salary < e1.Salary )  	 
	 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com