I have one table where multiple records inserted for each group of product. Now, I want to extract (SELECT) only the last entries. For more, see the screenshot. The yellow highlighted records should be return with select query.
Asked
Active
Viewed 564 times
4 Answers
2
The HAVING MAX and HAVING MIN clause for the ANY_VALUE function is now in preview
HAVING MAX
and HAVING MIN
were just introduced for some aggregate functions - https://cloud.google.com/bigquery/docs/release-notes#February_06_2023
with them query can be very simple - consider below approach
select any_value(t having max datetime).*
from your_table t
group by t.id, t.product
if applied to sample data in your question - output is

Mikhail Berlyant
- 165,386
- 8
- 154
- 230
1
You might consider below as well
SELECT *
FROM sample_table
QUALIFY DateTime = MAX(DateTime) OVER (PARTITION BY ID, Product);
If you're more familiar with an aggregate function than a window function, below might be an another option.
SELECT ARRAY_AGG(t ORDER BY DateTime DESC LIMIT 1)[SAFE_OFFSET(0)].*
FROM sample_table t
GROUP BY t.ID, t.Product
Query results

Jaytiger
- 11,626
- 2
- 5
- 15
-
Thank you @jaytiger. Can you guide me which book I must buy to learn BigQuery? – James Bond Feb 06 '23 at 10:20
-
1You can consider this book https://www.amazon.com/Google-BigQuery-Definitive-Warehousing-Analytics/dp/1492044466 but the best place to learn BigQuery, I believe, is the official document https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax and here on StackOverflow. – Jaytiger Feb 07 '23 at 03:45
0
You can use window function to do partition based on key and selecting required based on defining order by field.
For Example:
select * from (
select *,
rank() over (partition by product, order by DateTime Desc) as rank
from `project.dataset.table`)
where rank = 1

Mohammad
- 605
- 3
- 9
0
You can use this query to select last record of each group:
Select Top(1) * from Tablename group by ID order by DateTime Desc

Sahar Rezazadeh
- 314
- 3
- 12