Gordon Linoff
2020-11-29 20:19:05
In BigQuery, you can use aggregation:
select array_agg(t order by count desc limit 1)[ordinal(1)].*
from t
group by id;
What this does is construct an array of the full records for each id. But this array is ordered by the largest count first -- and only the first element of the array is used. The [ordinal(1)].* is just a convenient way to return the record fields as separate columns.
The more canonical method in SQL would be:
select t.* except (seqnum)
from (select t.*,
row_number() over (partition by id order by count desc) as seqnum
from t
) t
where seqnum = 1;
热门帖子
热门github
6