title: Scalable Solution to get latest row for each ID in BigQuery
body: <p>I have a quite large table with a field <code>ID</code> and another field as <code>collection_time</code>. I want to select latest record for each ID. Unfortunately combination of <code>(ID, collection_time)</code> time is not unique together in my data. I want just one of records with the maximum <code>collection time</code>. I have tried two solutions but none of them has worked for me:</p>
  
  <p><strong>First:</strong> using query</p>
  
  <pre><code>SELECT *  FROM 
  (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY collection_time) as rn 
  FROM mytable)  where rn=1
  </code></pre>
  
  <p>This results in <code>Resources exceeded</code> error that I guess is because of <code>ORDER BY</code> in the query.</p>
  
  <p><strong>Second</strong>
  Using join between table and latest time:</p>
  
  <pre><code>(SELECT tab1.* 
  FROM mytable AS tab1
  INNER JOIN EACH 
  (SELECT ID, MAX(collection_time) AS second_time 
  FROM mytable GROUP EACH BY ID) AS tab2
  ON tab1.ID=tab2.ID AND tab1.collection_time=tab2.second_time) 
  </code></pre>
  
  <p>this solution does not work for me because <code>(ID, collection_time)</code> are not unique together so in <code>JOIN</code> result there would be multiple rows for each <code>ID</code>.</p>
  
  <p>I am wondering if there is a workaround for the resourcesExceeded error, or a different query that would work in my case?</p>