Select newest record based on 2 criteria/groups

Transact-SQL
1

How can I get the newest record (redshiftcreatedate) based on shipmentnumber and stoptype? I get 2 return stops when a user adds/deletes delivery stops. The update doesn't overwrite the original record because there is a difference in mileage. Below is a sample of the data, the highlighted row would be the one I would like returned.

image
image728×164 6.17 KB

2 
;WITH cte AS
(
    SELECT
        RN = ROW_NUMBER() OVER (PARTITION BY shipmentnumber, stoptype ORDER BY redshiftcretedate),
        *
    FROM
        ThatTable
)
SELECT *
FROM
    cte 
WHERE
    RN = 1
    OR stoptype <> 'return';