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

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

The result should look like-

A_ID          B_ID        count
123           aaaa        3000
456           bbbb        6000

How to achieve this result?

Answer

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

Advertisement

Answer