How to get row with largest value?

Question

What I thought would work is:

SELECT *
FROM customer_sale
WHERE sale_date < '2019-02-01'
GROUP BY customer_id
HAVING sale_date = MAX(sale_date)

But running this results in an error

HAVING clause expression references column sale_date which is neither grouped nor aggregated

Is there another way to achieve this in Spanner? And more generally, why isn't the above allowed?

Edit

Example of data in customer_sale table:

customer_id         sale_date
-------------------------------
    1                  Jan 15
    1                  Jan 30
    1                  Feb 2
    1                  Feb 4
    2                  Jan 15
    2                  Feb 2

And the expected result:

customer_id         sale_date
-------------------------------
    1                  Jan 30
    2                  Jan 15

Answer 1

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions

This is the test table I am using:

index, customer_id, sale_date 

1   1   2017-08-25T07:00:00Z 

2   1   2017-08-26T07:00:00Z 

3   1   2017-08-27T07:00:00Z 

4   1   2017-08-28T07:00:00Z 

5   2   2017-08-29T07:00:00Z 

6   2   2017-08-30T07:00:00Z

With this query:

 Select customer_id, max(sale_date) as max_date
 from my_test_table
 group by customer_id;

I get this result:

 customer_id    max_date

 1              2017-08-28T07:00:00Z

 2              2017-08-30T07:00:00Z

Also including where statement:

 Select customer_id, max(sale_date) as max_date
 from my_test
 where sale_date < '2017-08-28'
 group by customer_id;