Tags

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


T-SQL Query | [Department Wise 2nd Highest Salary Puzzle ]  – In this puzzle we have to find the 2nd highest salary from each department. Please check out the sample input and expected output for details.

Sample Input

Department Table

DeptID DeptName
1 Finance
2 IT
3 HR

Emps Table

EmpID EmpName DeptID EmpSalary
101 Isha 1 7000
111 Esha 1 8970
102 Mayank 1 8900
103 Ramesh 2 4000
104 Avtaar 2 9000
105 Gopal 3 17000
106 Krishna 3 1000
107 Suchita 3 7000
108 Ranjan 3 17900

Expected Output

EmpID EmpName EmpSalary DeptName
102 Mayank 8900 Finance
104 Avtaar 9000 IT
107 Suchita 7000 HR

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table

CREATE TABLE Department
(
DeptID INT
,DeptName VARCHAR(10)
)
GO

--Insert Data

INSERT INTO Department(DeptID,DeptName)
VALUES
(1,'Finance'),
(2,'IT'),
(3,'HR')

--Verify Data

SELECT DeptID,DeptName FROM Department

--Create Table

CREATE TABLE Emps
(
EmpID INT
,EmpName VARCHAR(50)
,DeptID INT
,EmpSalary FLOAT
)
GO

--Insert Data

INSERT INTO Emps(EmpID,EmpName,DeptID,EmpSalary) VALUES
(101,'Isha',1,7000),
(111,'Esha',1,8970),
(102,'Mayank',1,8900),
(103,'Ramesh',2,4000),
(104,'Avtaar',2,9000),
(105,'Gopal',3,17000),
(106,'Krishna',3,1000),
(107,'Suchita',3,7000),
(108,'Ranjan',3,17900)

--Verify Data

SELECT EmpID,EmpName,DeptID,EmpSalary FROM Emps

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


;WITH CTE AS
(
	SELECT EmpID,EmpName,DeptID,EmpSalary 
		   ,RANK() OVER (PARTITION BY DEPTID ORDER BY EmpSalary DESC) rnk	
	FROM Emps
)
SELECT EmpID,EmpName,c.DeptID,EmpSalary,d.DeptName FROM CTE c INNER JOIN Department d ON d.DeptID = c.DeptID
WHERE rnk = 2

--

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

Keep Learning

http://MSBISkills.com