Need to get all records in table A and only one in table B which is the last updated
I need to get certain records in TABLE_A - then you need to display the last time the row that is in TABLE_B was updated (however, there are many records that correlate in TABLE_B). TABLE_A TABLE_A.PK - identifier and links to TABLE_B via TABLE_B.LINK where schema will be:
TABLE_A =================== ID NUMBER DESC VARCHAR2 TABLE_B =================== ID NUMBER LINK NUMBER LAST_DATE DATE
And the actual table data would be:
TABLE_A =================== 100 DESCRIPTION0 101 DESCRIPTION1 TABLE_B =================== 1 100 12/12/2012 2 100 12/13/2012 3 100 12/14/2013 4 101 12/12/2012 5 101 12/13/2012 6 101 12/14/2013
So, I need to read something:
Result ==================== 100 DESCRIPTION0 12/14/2013 101 DESCRIPTION1 12/14/2013
I tried different ways, but nothing works:
select * from
(SELECT ID, DESC from TABLE_A WHERE ID >= 100) TBL_A
full outer join
(select LAST_DATE from TABLE_B WHERE ROWNUM = 1 order by LAST_DATE DESC) TBL_B
on TBL_A.ID = TBL_B.LINK;
source to share
If you just want the most recent date, consider merging and join. Additional levels of subqueries don't help. Something like:
select a.id, a.desc, max(last_date)
from table_a a join
table_b b
on a.id = b.link
where a.id >= 100
group by a.id, a.desc;
Note. I doubt it is necessary full outer join
, although you can keep this if you have keys that don't match between tables. Perhaps it fits left join
.
I must point out that if you want more margins out b
, then your original slope is row_number()
correct. But the request will look like this:
select a.id, a.desc, max(last_date)
from table_a a left join
(select b.*, row_number() over (partition by link order by last_date desc) as seqnum
from table_b b
) b
on a.id = b.link and b.seqnum = 1
where a.id >= 100
group by a.id, a.desc;
source to share