SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method

Puzzle Statement

Recently I was doing something and one of my senior asked me how we can find department wise 2nd highest salary using Off Set and fetch method.

Sample Input

 EmpId EmpName DepId EmpSalary 1 1 5 2 1 10 3 1 15 4 1 20 5 1 25 6 1 30 7 1 35 8 1 40 9 1 45 10 2 50 11 2 55 12 2 60 13 2 65 14 2 70 15 2 75 16 2 80 17 2 85 18 2 90 19 2 95 20 3 100 21 3 105 22 3 110 23 3 115 24 3 120 25 3 125 26 3 130 27 3 135 28 3 140 29 3 145 30 4 150 31 4 155 32 4 160 33 ! 4 165 34 ,4,170

Expected output

 Depid SecondHighestSalary 1 40 2 90 3 140 4 190 5 240 6 290 7 340 8 390 9 440 10 490

Script

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

 ```-- CREATE TABLE EmployeeMaxSalary ( EmpId INT PRIMARY KEY ,EmpName VARCHAR(1) ,DepId int ,EmpSalary INT ) GO INSERT INTO EmployeeMaxSalary SELECT DISTINCT Number , CHAR(Number) ,1+Number/10 ,Number * 10 / 2 FROM MASTER..SPT_VALUES WHERE number > 0 AND number < 100 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 #

 ```-- SELECT e.Depid, ( SELECT p.EmpSalary FROM EmployeeMaxSalary p WHERE p.depid = e.depid ORDER BY p.EmpSalary DESC OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY ) SecondHighestSalary FROM EmployeeMaxSalary e GROUP BY e.depid -- ```

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 Kumar Khowal

Http://MSBISkills.com