Tags

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


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

Advertisements