CODEWITHSUNDEEP
sqlgoogle-bigquery
arcee123
arcee123

Reputation: 211

get the max record out of a google big query

I have a table with 198Mil records.

I am using this query to get the LATEST records for each ID:

with cte as (
select *, row_number() OVER (Partition by ATTOM_ID ORDER BY LastLoadDate DESC) rnum from `mother-216719.PROPERTY.ATTOM_DETAIL`
) Select * from cte where rnum = 1

Of note, there's 240 columns in this table.

This has been running over an hour, with no avail. Is there a way to make this work? Thanks!

Upvotes: 1

Views: 412

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Try below approach - usually it helps 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY LastLoadDate DESC LIMIT 1)[OFFSET(0)]
FROM `mother-216719.PROPERTY.ATTOM_DETAIL` t
GROUP BY ATTOM_ID

Upvotes: 3

Related Questions