SQL Exercise – 17

SQL Exercise:

You are given an Employee table containing details about employees, their departments, and their salaries. Your task is to write an SQL query to find the top 2 highest salaries for each department.

Create table script:

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);

Data insertion script:

INSERT INTO Employee (EmpID, EmpName, Department, Salary) VALUES
(1, 'Amit', 'HR', 60000),
(2, 'Neha', 'HR', 60000),
(3, 'Raj', 'HR', 50000),
(4, 'Sujoy', 'HR',45000),
(5, 'Pooja', 'IT', 90000),
(6, 'Ravi', 'IT', 90000),
(7, 'Anil', 'IT', 85000),
(8, 'Sonia', 'IT', 85000),
(9, 'Manoj', 'Finance', 70000),
(10, 'Kiran', 'Finance', 60000),
(11, 'Vivek', 'Finance', 60000),
(12, 'Sumit', 'Finance',55000)

Solution:

SELECT 
Department,
EmpName,
Salary
FROM (
SELECT 
Department,
EmpName,
Salary,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
FROM Employee
) AS RankedEmployees
WHERE rnk <= 2
ORDER BY Department, rnk;

Output:

Explanation:

• DENSE_RANK() Function:
  • DENSE_RANK() ranks rows within a partition (each department) based on Salary in descending order.
  • If there are duplicate salaries, they get the same rank, but the next rank doesn’t skip (e.g., 1, 1, 2).
• PARTITION BY:
  • This divides the data into partitions (in this case, by department), and the ranking is applied within each partition.
• Filter:
  • WHERE rnk<= 2 ensures that only the top 2 salaries (including ties) for each department are returned.
• ORDER BY:
  • The output is sorted by Department and rnk for clarity.