Pull latest record in table with duplicate ID's

Solution 1:

You can use LAG(), LEAD() ROW_NUMBER() function to achieve this goal. Check my below queries.

Using LAG():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LAG(DATE, 1) OVER(Partition By ID ORDER BY DATE DESC) AS DateOfPreviousRow
     FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using LEAD():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LEAD(DATE, 1) OVER(Partition By ID ORDER BY DATE) AS DateOfPreviousRow
    FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using ROW_NUMBER():

SELECT T.* FROM 
(
   SELECT 
       *,
       ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS ROWNumber
   FROM Table
) T
WHERE ROWNumber = 1