This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find the second highest salary for each department.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EmpID EmpName DeptID EmpSalary ----------- ---------- ----------- -------------------- 1 Rohan 1 90000 2 Luis 1 60000 3 Robort 2 10000 4 Markvel 2 30000 5 Manish 2 60000 6 Gopi 3 70000 7 Mayur 3 50000 8 Suko 3 80000 9 Raj 4 20000 10 Anvesh 4 10000 |
Expected Output:
|
1 2 3 4 5 6 |
EmpID EmpName DeptID EmpSalary ----------- ---------- ----------- -------------------- 2 Luis 1 60000 4 Markvel 2 30000 6 Gopi 3 70000 10 Anvesh 4 10000 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE Employee ( EmpID INT ,EmpName VARCHAR(10) ,DeptID INT ,EmpSalary BIGINT ) GO INSERT INTO Employee VALUES (1,'Rohan',1,90000) ,(2,'Luis',1,60000) ,(3,'Robort',2,10000) ,(4,'Markvel',2,30000) ,(5,'Manish',2,60000) ,(6,'Gopi',3,70000) ,(7,'Mayur',3,50000) ,(8,'Suko',3,80000) ,(9,'Raj',4,20000) ,(10,'Anvesh',4,10000) GO |
Solution: Use CTE with RANK()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
WITH CTE AS ( SELECT EmpID ,EmpName ,DeptID ,EmpSalary ,RANK() OVER (PARTITION BY DEPTID ORDER BY EmpSalary DESC) rnk FROM Employee ) SELECT EmpID ,EmpName ,DeptID ,EmpSalary FROM CTE WHERE rnk = 2 |
Please try the different solution for this puzzle and share it via comment...
