How to get the latest rows in BigQuery

user_15379556 0
Viewed 167

In BigQuery, how can I get rows based on the latest value of timestamp field?

For example, I have this table.

first_name last_name use_auto login_at
James Davis true 2021-05-13 02:00:00 UTC
James Moore true 2021-05-13 02:00:01 UTC
James Green true 2021-05-13 02:00:02 UTC
Edward Green false 2021-05-13 03:00:00 UTC
Edward Wilson false 2021-05-13 03:00:01 UTC
James Davis false 2021-05-13 03:00:00 UTC
James Moore false 2021-05-13 03:00:01 UTC
James Green false 2021-05-13 03:00:02 UTC
Edward Green true 2021-05-13 02:00:00 UTC
Edward Wilson true 2021-05-13 02:00:00 UTC

I'd like to get the result after query like this,

first_name last_name use_auto login_at
Edward Green false 2021-05-13 03:00:00 UTC
Edward Wilson false 2021-05-13 03:00:01 UTC
James Davis false 2021-05-13 03:00:00 UTC
James Moore false 2021-05-13 03:00:01 UTC
James Green false 2021-05-13 03:00:02 UTC

Please let me know what query should I use.

google-bigquery mysql sql
2 Answers

A convenient way in BigQuery uses aggregation:

select array_agg(t order by login_at desc)[ordinal(1)].*
from thistable t
group by first_name, last_name;
user_11440350

Following up on the Gordon's answer: add limit 1 to reduce memory consumption and increase scalability:

select array_agg(t order by login_at desc limit 1)[ordinal(1)].*
from thistable t
group by t.first_name, t.last_name;
user_134735250
Related