I have 2 tables in Big Query:

TABLE A

TABLE B

And I would like to link (left join on ID and Name) rows from table B to the max date of table A, to get :

How can I do that in SQL (Big Query) please ? Thanks

CodePudding user response：

I didn't tested it but I think you should left join the b table to a table in which the max date is indicated. Usage of condition pure on the left table is somewhat unusual though from the definition of left join I expect it to work.

select a_ranked.ID, a_ranked.Name, a_ranked.Date_A
     , a_ranked.field_x, a_ranked.field_y, a_ranked.field_z
     , b.field_A, b.field_B
from (
  select a.*, rank() over (partition by ID, Name order by Date_A desc) as r
  from a
) a_ranked
left join b on a_ranked.ID = b.ID and a_ranked.Name = b.Name and a_ranked.r = 1

CodePudding user response：

Consider below approach

select a.*, 
  (if(row_number() over win = 1, b, null)).* except(id, name, date_b)
from table_a a 
left join table_b b 
using(id, name)
window win as (partition by a.id, a.name order by date_a desc)    

if applied to sample data in your question - output is