一个选项是使用子查询进行筛选：

select t.*
from mytable t
where t.count = (select max(t1.count) from mytable t1 where t1.a_id = t.a_id)

也可以使用窗口功能：

select t.* except(rn)
from (
    select t.*, rank() over(partition by a_id order by count desc) rn
    from mytable t
) t
where rn = 1

可以在bigquery中使用聚合：

select array_agg(t order by count desc limit 1)[ordinal(1)].*
from t
group by a_id;

下面是bigquery标准sql

# standardSQL

SELECT AS VALUE ARRAY_AGG(t ORDER BY count DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY a_id

如果要应用于问题的样本数据，则输出为

Row a_id    b_id    count    
1   123     aaaa    3000     
2   456     bbbb    6000