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

Expected output

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