SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table yourtable
(DepartmentID int,
  Department varchar(100),
  EmployeeID int,
  rate int,
  RateModifiedDate date);
insert into yourtable values (16,'Executive',234,390600,'2009-01-31');
insert into yourtable values (16,'Executive',234,485577,'2011-11-14');
insert into yourtable values (16,'Executive',234,600962,'2012-01-29');
insert into yourtable values (16,'Executive',1,1255000,'2009-01-14');
4 rows affected

SELECT DepartmentID, Department, EmployeeID, rate,  RateModifiedDate
FROM ( 
SELECT DepartmentID, Department, EmployeeID, rate,  RateModifiedDate, 
RANK() OVER (PARTITION BY EmployeeID ORDER BY RateModifiedDate DESC) dest_rank
FROM yourtable
) sub 
WHERE dest_rank = 1
ORDER BY RateModifiedDate DESC;


DepartmentID
Department
EmployeeID
rate
RateModifiedDate


16
Executive
234
600962
2012-01-29

16
Executive
1
1255000
2009-01-14


SELECT 
DepartmentID, Department, EmployeeID, rate,  RateModifiedDate
FROM yourtable y1
WHERE RateModifiedDate = 
(SELECT MAX(y2.RateModifiedDate)
FROM yourtable y2
WHERE y1.EmployeeID = y2.EmployeeID)
ORDER BY RateModifiedDate DESC;


DepartmentID
Department
EmployeeID
rate
RateModifiedDate


16
Executive
234
600962
2012-01-29

16
Executive
1
1255000
2009-01-14