CODEWITHSUNDEEP
sqlsql-servert-sql
Sam
Sam

Reputation: 61

SQL Select rows with highest count

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.

Upvotes: 4

Views: 5056

Answers (2)

Tyron78
Tyron78

Reputation: 4187

I would suggest a Window Function:

WITH cte AS(
  select itemid, ordersupplierid, COUNT(OrderSupplierId) AS SupCount
  from purchaseorderline
  group by itemid, ordersupplierid
  order by ItemID
),
cteFilter AS(
SELECT itemid, ordersupplierid, SupCount, ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY SupCount DESC) rn
  FROM cte
)
SELECT itemid, ordersupplierid, SupCount
  FROM cteFilter
  WHERE rn = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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;

Upvotes: 2

Related Questions