BigQuery: running last value and table join -

table_1 sales table:

time | item | ... -----------------  1   |  x   | ...  1   |  y   | ...  2   |  x   | ...  4   |  x   | ...  6   |  x   | ...  6   |  y   | ... 

table_2 cost table

time | item | cost -----------------  1   |  x   |  1   |  y   | b  3   |  x   | c  4   |  x   | d  4   |  y   | e  5   |  x   | f 

what i'm trying achieve is:
for each row in table_1, latest cost value table_2 (i.e. @ most, table_1 row's time)

the result should this:

time | item | ... | cost ------------------------  1   |  x   | ... |  1   |  y   | ... | b  2   |  x   | ... |  4   |  x   | ... | d  6   |  x   | ... | f  6   |  y   | ... | e 

(i know it's straight forward traditional sql using subquery in select section or unequal joins, bigquery doesn't allow it)

try below:

select sales.time [time], sales.item item, cost  (   select sales.item, sales.time, cost,           cost.time - sales.time delta,          row_number() over(partition sales.item, sales.time order delta desc) win   table_1 sales   left join table_2 cost   on sales.item = cost.item   cost.time - sales.time <= 0 )  win = 1 order 1, 2 

should give result expect

time    item    cost         1       x               1       y       b         2       x               4       x       d         6       x       f         6       y       e