T-SQL – How to get Department-Wise Second Highest Salary (With Ties)

CREATE TABLE dbo.Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    Gender CHAR(1),
    Department VARCHAR(50),
    Salary INT,
    JoiningDate DATE,
    ManagerID INT,
    IsActive BIT
);

GO
INSERT INTO  dbo.Employee VALUES 
(101, 'Shaun Abbot',      'M', 'IT',        88000, '2020-03-15', NULL, 1),
(102, 'Stefee Taylor',   'F', 'HR',        88000, '2020-07-01',  105, 1),
(103, 'Mathew Bulmer',   'M', 'Finance',   75000, '2021-01-20',  104, 1),
(104, 'Jennat Warner',    'F', 'Finance',   98000, '2021-10-05', NULL, 1),
(105, 'Jonathan Perry',     'M', 'HR',        95000, '2019-12-12', NULL, 1),
(106, 'Lisa Devine',     'F', 'IT',        85000, '2021-06-11', 101, 1),
(107, 'Adam Trumen',     'M', 'IT',        50000, '2019-04-01', 101, 1),
(108, 'Sarah Mclean',      'F', 'Marketing', 95000, '2023-01-11', NULL, 1),
(109, 'Steve Maxwell',      'M', 'IT',        75000, '2025-12-01', 101, 0),
(110, 'Georgia Havells',   'F', 'Finance',   45000,   '2025-09-01', 104, 1);

WITH DeptSalaries AS (
  SELECT *,
         DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
  FROM Employee
  WHERE Salary IS NOT NULL
)
SELECT * FROM DeptSalaries WHERE Rank = 2;

SELECT *
FROM Employee e
WHERE Salary = (
  SELECT MAX(Salary)
  FROM Employee
  WHERE Department = e.Department
    AND Salary < (
      SELECT MAX(Salary)
      FROM Employee
      WHERE Department = e.Department
    )
);