Avoid duplication in a join (Oracle)

Question

I have to join 2 tables.

I have to found the parameter Value in the table 2 to add this to my table 1. But this is a historical table, so is in the form:

object  value   date 
1       232     24/10/2020    
1       111     11/06/2019          
2       231     22/09/2011 
2       545     05/09/2020
...     ...     ...

How I can write the join query to avoid duplication and take only the value for the last date?

I have tried some like:

select a.*, b.value
from tableA a, (select value, object max(date) from tableB group by object, value)b
where a.object = b.oject

But is wrong

Thank you very much.

Answer 1

You can use row_number() to generate a sequential number and then choose the first one:

select a.*, b.value
from tableA a join
     (select b.*, row_number() over (partition by object order by date desc) as seqnum
      from tableB
     ) b
     on a.object = b.object and seqnum = 1;

Note: Learn to use proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.