How can I return one single row for each customer based on the latest date in BigQuery?

Question

I have a table which contains data in the following structure.

customer_id;   status;   date
1;             1;       01-01-2019
1;            3;       01-02-2019
2;             4;        01-02-2019
2;             3;        01-03-2019

What I want to return is:

customer_id;   status;   date
1;            3;       01-02-2019
2;             3;        01-03-2019

So far I have worked with a max statement in the select clause for the date.

select    distinct customer_id,
          status,
          max(date) as date_max
from *table*
group by customer_id

This returns the error: Unexpected keyword GROUP

Hopefully you can help! Kind regards.

Answer 1

This query should do it:

select t.customer_id, t.status, t.date
from YourTable t
inner join (select customer_id, max(date) as date
            from YourTable 
            group by customer_id) a
on (t.customer_id= a.customer_id and t.date = a.date)