Scalable Solution to get latest row for each ID in BigQuery
<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>