Choose from unique IDs with the latest timestamp
I have a table in Big Query with unique IDs, timestamps and distances, and would like to select one record by ID with the newest timestamp.
eg. the table looks like
ID|timestamp|distance
A|100|2
A|90|3
B|110|5
D|100|4
A|80|2
B|10|2
The request should return something like:
A|100|2
B|110|5
D|100|4
A working query in PostgreSQL looks like this, but there is no "great ON" in bigquery?
SELECT * FROM (
SELECT DISTINCT ON (ID)
id, timestamp, distance
FROM ranking
ORDER BY ID, timestamp DESC
) AS latest_dtg
ORDER BY distance
+3
source to share
3 answers
Below is for standard SQLQuery SQL
#standardSQL
SELECT row.* FROM (
SELECT ARRAY_AGG(r ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)] AS row
FROM ranking AS r
GROUP BY id
)
You can play / test with below dummy data from your question.
#standardSQL
WITH ranking AS (
SELECT 'A' AS id, 100 AS timestamp, 2 AS distance UNION ALL
SELECT 'A', 90, 3 UNION ALL
SELECT 'B', 110, 5 UNION ALL
SELECT 'D', 100, 4 UNION ALL
SELECT 'B', 10, 2 UNION ALL
SELECT 'A', 80, 2
)
SELECT row.* FROM (
SELECT ARRAY_AGG(r ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)] AS row
FROM ranking AS r
GROUP BY id
)
+3
source to share
Here's one idea:
#standardSQL
WITH ranking AS
(SELECT 'A' id, 100 ts, 2 distance UNION ALL
SELECT 'A', 90, 3 UNION ALL
SELECT 'B', 110, 5 UNION ALL
SELECT 'D', 100, 4 UNION ALL
SELECT 'B', 10, 2 UNION ALL
SELECT 'A', 80, 2)
SELECT id, ARRAY_AGG(STRUCT(ts, distance) ORDER BY ts DESC LIMIT 1)[SAFE_OFFSET(0)]
FROM ranking
GROUP BY id
+1
source to share