1

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

1
  • the duplicate rows in the second table was a mistake on my part, sorry
    – Levicia
    Jan 31, 2019 at 11:43

2 Answers 2

1

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
 ;
2
  • Using ur example and using it almost 1:1, i keep getting the error of column ambigously defined, however i can't see why
    – Levicia
    Jan 31, 2019 at 12:08
  • You need to keep only the columns you need in the subquesry You have a.id and b.id (and a.name and b.name), so at least two columns with same name.keep only one of them in the subquery. See my edit. Jan 31, 2019 at 12:16
0

You can use aggregation together with "KEEP LAST". It will pick last value of column from a group ordered by another column.

select a.id
  , a.name
  , max(b.preference) KEEP (DENSE_RANK LAST ORDER BY A.TIMESTAMP)
  , max(a.timestamp)
from A
join B on (a.id = b.id and a.name=b.name)
group by a.id, a.name

PS: your example seems to be ambiguous is "name" also a key?

1
  • No name is not supposed to be a key, as 2 completely random people might have the same name, sorry for the ambiguity
    – Levicia
    Jan 31, 2019 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.