Bigquery: how to select mutiple columns from a where clause

user_11162361 0
Viewed 73

I am doing something like this in BigQuery:

SELECT
    user_id,
    title,
    time
FROM
    abc
where (user_id, time) IN (select user_id, max(time)
                          from abc
                          group by user_id)

But I am getting this error: 'Subquery of type IN must have only one output column'

How could we do this in BigQuery? Thanks in advance.

google-bigquery
1 Answers

Below will work (note addition of as struct)

SELECT
    user_id,
    title,
    time
FROM
    abc
where (user_id, time) IN (select as struct user_id, max(time)
                          from abc
                          group by user_id)

Meantime, below is more BigQuery'ish way of doing the same

select as value
  array_agg(t order by time desc limit 1)[offset(0)]
from abc t
group by user_id
user_52219440
Related