sqldategoogle-bigquerymaxdate

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


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.


Solution

  • 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)