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

ID EmpName Salary
1 P 100
2 Q 500
3 R 230
4 S 1000
5 L 670

Expected Output

ID EmpName Salary
3 R 230
5 L 670

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”.

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