CODEWITHSUNDEEP
sqlgoogle-bigquery
andrew
andrew

Reputation: 619

how get rows with latest date?

I have this table in BigQuery 

country, date, confirmed    
Afghanistan, 2020-03-16, 21
Afghanistan, 2020-03-17, 22
Afghanistan, 2020-03-18, 22
Albania, 2020-03-16, 23
Albania, 2020-03-17, 33
Albania, 2020-03-18, 38
...

and I would like to show all rows per country with latest date, however this command not working >

SELECT * FROM table where date=max(date) > Aggregate function MAX not allowed in WHERE clause at.

output should be:

Afghanistan, 2020-03-18, 22
Albania, 2020-03-18, 38

Any idea pls ?

Upvotes: 2

Views: 2282

Answers (2)

Mr. Lance E Sloan
Mr. Lance E Sloan

Reputation: 3387

As I commented on the accepted solution to this question, there must be a better syntax than the one used in that solution.

The following solution works for me. It may not be the most elegant, efficient, or "best" solution, but it is easy to understand.

# ---- begin example data
WITH `project.dataset.table` AS (
  SELECT 'Afghanistan' country, DATE '2020-03-16' day, 21 confirmed UNION ALL
  SELECT 'Afghanistan', '2020-03-17', 22 UNION ALL
  SELECT 'Afghanistan', '2020-03-18', 22 UNION ALL
  SELECT 'Albania', '2020-03-16', 23 UNION ALL
  SELECT 'Albania', '2020-03-17', 33 UNION ALL
  SELECT 'Albania', '2020-03-18', 38 
)
# ---- end example data
# ---- begin query
SELECT *
FROM `project.dataset.table` AS t
WHERE t.`day`=(SELECT max(t.`day`) FROM `project.dataset.table` AS t)
GROUP BY t.country, t.`day`, t.confirmed
# ---- end query

It uses a subquery to find the maximum date and use that in the where clause of the main query.

I tried to produce a self-contained example with data as the accepted solution did. However, the subquery was unable to access the data provided by the WITH clause. There may be a better way to structure the example, but at least this query works and is easier to understand.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY day DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY country

if to apply to sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Afghanistan' country, DATE '2020-03-16' day, 21 confirmed UNION ALL
  SELECT 'Afghanistan', '2020-03-17', 22 UNION ALL
  SELECT 'Afghanistan', '2020-03-18', 22 UNION ALL
  SELECT 'Albania', '2020-03-16', 23 UNION ALL
  SELECT 'Albania', '2020-03-17', 33 UNION ALL
  SELECT 'Albania', '2020-03-18', 38 
)
SELECT AS VALUE ARRAY_AGG(t ORDER BY day DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY country

output is 

Row country         day         confirmed    
1   Afghanistan     2020-03-18  22   
2   Albania         2020-03-18  38

Upvotes: 6

Related Questions