返回最高计数记录 [英] Return the highest count record

问题描述

我正在处理的数据如下-

The data I am working on looks like below-

A_ID          B_ID           count
123           abcd          1000
123           aaaa          2000
123           aaaa          3000
456           null          50
456           bbbb          6000
456           cccc          450

我希望能够提取给定A_id中具有最高计数的B_id

I want to be able to extract the B_id that has the highest count for a given A_id

结果应类似于-

A_ID          B_ID        count
123           aaaa        3000
456           bbbb        6000

如何获得此结果?

推荐答案

一种选择是使用子查询进行过滤:

One option is to filter with a subquery:

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

您还可以使用窗口功能:

You can also use window functions:

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

这篇关于返回最高计数记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！