How to create a view with unique record based on date

I have an EMPLOYEE table that looks like this: EmployeeID,Name,PositionID,HireDate,PositionStatus E001,Chelsey,P123,2013-11-23,Terminated E001,Chelsey,P234,2019-03-13,Active E002,Mike,P345,2014-03-...

olafhelper

Apr 02, 2025

Next time please post table design as DDL, some sample data as DML statement and the expected result.

Group on EmployeeID to get the highest hire date and then join on.

;WITH cte AS
    (SELECT EmployeeID, MAX(HireDate) AS MaxHireDate
     FROM yourTable
     GROUP BY EmployeeID)
SELECT *
FROM cte
     INNER JOIN
     yourTable AS T
         ON cte.EmployeeID = T.EmployeeID
            AND cte.MaxHireDate = T.HireDate

;WITH cte AS (SELECT EmployeeID, MAX(HireDate) AS MaxHireDate FROM yourTable GROUP BY EmployeeID) SELECT * FROM cte INNER JOIN yourTable AS T ON cte.EmployeeID = T.EmployeeID AND cte.MaxHireDate = T.HireDate

Forum Discussion

How to create a view with unique record based on date

Resources