SQL - Select newest record when there's a duplicate

Question

I'm writing a query, and have used the solution in this thread and it has worked somewhat but I'm still having a bit of trouble.

I want to select a bunch of records that are for the most part unique, but there are a few that occur twice. I want to be able to only select the record that has the newest date. This is my query so far:

SELECT tb1.id,tb1.type,tb1.date

FROM Table tb1  

WHERE tb1.type = 'A' OR 'B'
AND tb1.date = (SELECT TOP 1 tb2.date
               FROM Table tb2  
               WHERE tb2.date = tb1.date
                ORDER BY tb2.date DESC)

This works as far as it retrieves the latest record of those records that have duplicates, but those records that DON'T have duplicates don't appear at all.

Thanks for any help, and my apologies if I've missed something super obvious; I'm new to the SQL game.

Answer 1

There's a few ways to do it, one way is to use ROW_NUMBER like this:

SELECT id, type, date
FROM 
(
    SELECT tb1.id, tb1.type, tb1.Date, 
        ROW_NUMBER() OVER (PARTITION BY tb1.id ORDER BY tb1.Date DESC) AS RowNo
    FROM Table tb1
    WHERE tb1.type IN ('A','B')
) x
WHERE x.RowNo = 1

This will return the row for each distinct id value that has the latest Date.