CODEWITHSUNDEEP
sqlgoogle-bigquery
Jose Calderon
Jose Calderon

Reputation: 35

HOW TO RETURN THE MOST RECENT VALUE?

timestamp id type
2021-06-01T00:00:00 ID1 LOL
2021-06-01T00:00:01 ID2 DOTA
2021-06-01T00:00:02 ID2 DOTA
2020-06-02T00:00:00 ID5 COD
2020-06-02T00:00:01 ID7 VALO
2020-06-02T00:00:02 ID8 VALO
2020-06-02T00:00:03 ID3 DOTA
2020-06-03T00:00:00 ID1 DOTA
2020-06-03T00:00:01 ID1 DOTA
2020-06-03T00:00:02 ID2 DOTA
2020-06-03T00:00:03 ID3 DOTA
2020-06-03T00:00:04 ID4 LOL

I am trying to get a result for all DISTINCT IDS with the most recent type.

Using this query

SELECT DISTINCT id, type
FROM table1
ORDER BY 1;

I got this result:

id type
ID1 DOTA
ID1 LOL
ID2 DOTA
ID3 DOTA
ID4 LOL
ID5 COD
ID7 VALO
ID8 VALO

I understand that the row 1 and row 2 have distinct values. I tried querying this to check if it will only return a value with the most recent date. It didn't.

SELECT DISTINCT id, type, MAX(timestamp) date
FROM table1
GROUP BY 1, 2
ORDER BY 1;
id type date
ID1 DOTA 2020-06-03 00:00:01
ID1 LOL 2021-06-01 00:00:00
ID2 DOTA 2021-06-01 00:00:02
ID3 DOTA 2020-06-03 00:00:03
ID4 LOL 2020-06-03 00:00:04
ID5 COD 2020-06-02 00:00:00
ID7 VALO 2020-06-02 00:00:01
ID8 VALO 2020-06-02 00:00:02

Tried below query just to check. Returned the same.

SELECT DISTINCT id, type, (SELECT MAX(timestamp) FROM table1 as b where b.timestamp = a.timestamp ) 
FROM table1 as a
GROUP BY 1, 2
ORDER BY 1;

Is my intended result possible?

Upvotes: 1

Views: 1499

Answers (4)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Consider below options

select as value array_agg(t order by timestamp desc limit 1)[offset(0)]
from `project.dataset.table1` t
group by id

or

select *
from `project.dataset.table1` t
where true 
qualify row_number() over(partition by id order by timestamp desc) = 1

both with below output

enter image description here

Upvotes: 1

Dri372
Dri372

Reputation: 1321

Just use GROUP BY

SELECT id, type, MAX(date) AS date_max FROM table1 GROUP BY id,type ORDER BY id,type;

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76424

This provides you groups:

SELECT DISTINCT id, type, MAX(timestamp) date
FROM chinook.new_table as a
GROUP BY 1, 2
ORDER BY 1;

However, seemingly you want a single group for each id, so you will need to somehow aggregate type as well:

SELECT DISTINCT id, GROUP_CONCAT(`type`), MAX(timestamp) date
FROM chinook.new_table as a
GROUP BY 1
ORDER BY 1;

The above will group types into comma-separated values.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

A simple method uses qualify:

SELECT a.*
FROM chinook.new_table as a
WHERE true
QUALIFY ROW_NUMBER() OVER (PARTITION BY id, type ORDER BY date DESC) = 1;

You can also express this easily using GROUP BY:

SELECT ARRAY_AGG(a ORDER BY date DESC LIMIT 1).*
FROM chinook.new_table a
GROUP BY id, type;

Upvotes: 1

Related Questions