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