CODEWITHSUNDEEP
sqlgoogle-bigquery
James Bond
James Bond

Reputation: 19

BigQuery - Extract last entry of each group

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.

enter image description here

Upvotes: 1

Views: 1634

Answers (4)

Jaytiger
Jaytiger

Reputation: 12234

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

enter image description here

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

enter image description here

Upvotes: 3

Sahar Rezazadeh
Sahar Rezazadeh

Reputation: 333

You can use this query to select last record of each group:

Select Top(1) * from Tablename group by ID order by DateTime Desc

Upvotes: 0

Mohammad
Mohammad

Reputation: 678

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

Upvotes: 0

Related Questions