Comparing duplicates and choosing the duplicate with most recent date in a join

duplicationjoin;oracleoracle-12c

Assume i have table A:

id:    name:    timestamp:
1      Bob      2018-05-31 04:46:37
2      Alice    2019-01-31 07:13:56
1      Bob      2019-01-15 18:23:74

And i want to join with another table B:

id:    name:    Preference:
1      Bob      Car
2      Alice    Bike

ID alone isnt a primary key. however ID + timestamp IS an unique key. However, how would i make to only join the 1 part of the duplicate, that has a the newest date? So that i would get a joined table like:

id:    name:    Preference:    Timestamp:
1      Bob      Car            2019-01-15 18:23:34
2      Alice    Bike           2019-01-31 07:13:56

By if i have duplicates, then compare the date between the duplicates and choose the newst

Best Answer

You could use a LATERAL join or CROSS APPLY (both added in version 12c) or window ranking functions in a subquery which should work in older versions as well:

WITH cte AS
( 
    SELECT 
        b.id, b.name, b.preference, 
        a.timestamp,         -- keep only columns you need
        ROW_NUMBER() OVER (PARTITION BY b.id
                           ORDER BY a.timestamp DESC)
          AS rn
    FROM b 
         LEFT JOIN a
         ON b.id = a.id
)
SELECT cte.*           -- keep only columns you need
FROM cte
WHERE rn = 1
-- ORDER BY b.id
 ;
Related Question