title: Cross apply MSSQL operator equivalent in bigquery
body: <p>is there an equivalent to cross apply in Sql server syntaxe or in bigquery syntaxe.</p>
  
  <p>I need to write this query without the cross apply:</p>
  
  <pre><code>   select *
      from t1 
      cross apply (select top 1 col1,col2,col3
      from t2
      where col1 = t1.col1
      and (col2 = '0' or col2 = t1.col2)
  and (col3 = '0' or (col3 = left(t1.col3)  and t1.col4 = 'fr'))
      order by col2 desc, col3 desc)
  </code></pre>
  
  <p>t1 called 'Delivery' contains deliveries informations: </p>
  
  <pre><code>Delivery_ID,Delivery_ShipmentDate,Country_Code,address_ZipCode and the providerservice_id
  </code></pre>
  
  <p>t2 called 'ProviderService' contains informations about the providerservice: </p>
  
  <pre><code>Providerservice_id,DCountry_ID , DZone_Code  as well as a numeric ProviderService_DeliveryTime.
  </code></pre>
  
  <p>So each Delivery_ID has a ProviderService_ID
  And For the same Providerservice we could have several distinct ProviderService_DeliveryTime according to the other columns in this table(DCountry_ID , DZone_Code)</p>
  
  <p>So the final query will be: </p>
  
  <pre><code>    Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime
      from Delivery t1
      cross apply (select top 1 ProviderService_DeliveryTime,DCountry_ID , DZone_Code 
  from ProviderService 
      where ProviderService_id = t1.ProviderService_id
  And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
  And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR'))
          order by t2.DCountry_ID desc, t2.DZone_Code desc)sq
  </code></pre>
  
  <p>In fact I need first to write the same query in sql server syntaxe and then to write it in Bigquery because BigQuery don't recognize "cross apply" operator.</p>
  
  <p>I tried to do it with row_number window function but it did not work:</p>
  
  <pre><code>    with cte as (Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime,row_number() over (partition by t2.providerservice_id order by t2.DCountry_ID desc, t2.DZone_Code desc) as num
      from Delivery t1
      inner join providerservice t2 on t1.providerservice_id = t2.providerservice_id
  And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
  And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR')))
  
  
  select * from cte where num = 1
  </code></pre>
  
  <p>it works only when I filter by delivery_id:</p>
  
  <pre><code> with cte as (Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime,row_number() over (partition by t2.providerservice_id order by t2.DCountry_ID desc, t2.DZone_Code desc) as num
      from Delivery t1
      inner join providerservice t2 on t1.providerservice_id = t2.providerservice_id
  And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
  And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR'))
  where delivery_id = xxxx)
  
  
  select * from cte where num = 1
  </code></pre>
  
  <p>Can any one help me please? Thanks!!</p>