CODEWITHSUNDEEP
google-bigquery
MPA
MPA

Reputation: 1117

Bigquery - select last # distinct values

Given table as:

WITH table AS
(SELECT 'A' id, '11' ar, 1 ts   UNION ALL
SELECT 'A', '12', 2 UNION ALL
SELECT 'A', '11', 3 UNION ALL
SELECT 'B', '11', 4 UNION ALL
SELECT 'B', '13', 5 UNION ALL
SELECT 'B', '12', 6 UNION ALL
SELECT 'B', '12', 7)


id  ar  ts
A   11  1
A   12  2
A   11  3
B   11  4
B   13  5
B   12  6
B   12  7

I need to get the unique last two rows as:

id  ar
A   11
A   12
B   12
B   13

I tried ARRAY_AGG with DISTINCT and LIMIT,

But the ORDER BY must be the same as expression

Upvotes: 0

Views: 459

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(ars) 
FROM (
  SELECT id, ARRAY_AGG(ar ORDER BY ts DESC LIMIT 2) AS ars
  FROM (
    SELECT id, ar, MAX(ts) AS ts 
    FROM `project.dataset.table`
    GROUP BY id, ar
  )
  GROUP BY id
) t, t.ars AS ar

if to apply to sample data from your question - output is

Row id  ar   
1   A   11   
2   A   12   
3   B   12   
4   B   13

Upvotes: 1

Related Questions