SQL Select rows with highest count

Question

I'm trying to select only the rows with highest COUNT() based on two fields.

First I have a query that gives me the values of how many times a specific ITEM-SUPPLIER combo has occurred in my table.

select itemid, ordersupplierid, COUNT(OrderSupplierId) AS SupCount from purchaseorderline
group by itemid, ordersupplierid
order by ItemID

In the results I have:

itemid   | ordersupplierid | SupCount
15850    |  579            |  1
15850    |  587            |  3
15850    |  605            |  2
15851    |  616            |  5
15852    |  579            |  1
15852    |  587            |  2
15854    |  616            |  11
15855    |  616            |  1

So from there I would need to get only rows:

itemid   | ordersupplierid | SupCount
15850    |  587            |  3
15851    |  616            |  5
15852    |  587            |  2
15854    |  616            |  11
15855    |  616            |  1

As in the ItemId - OrderSupplierId combo with the highest SupCount.

Anyone have an idea on how to do this? It would be greatly appreciated.

Answer 1

You can use window functions:

select itemid, ordersupplierid, supcount
from (select itemid, ordersupplierid, count(*) AS SupCount,
             max(count(*)) over (partition by itemid) as maxcount
      from purchaseorderline
      group by itemid, ordersupplierid
     ) io
where supcount = maxcount
order by ItemID;

If there are ties, then this will return all matching rows. If you want just one row (even when there are ties), then you can use row_number():

select itemid, ordersupplierid, supcount
from (select itemid, ordersupplierid, count(*) AS SupCount,
             row_number() over (partition by itemid order by count(*) desc) as seqnum
      from purchaseorderline
      group by itemid, ordersupplierid
     ) io
where seqnum = 1
order by ItemID;