T-SQL Group By Problem

Question

I've got the following Problem (or maybe just a thinking barrier):

I've got a table (actually a view from a table) with the following columns and data:

Now i want to Group this data by the column "Customer" and get the "CompetitorName" with the highest "CompetitorCount". Of course i can create a query like this:

SELECT Customer, MAX(CompetitorCount) FROM MyTable GROUP BY Customer

This will return two rows:

Foo; 12  
Bar; 7

But i wont be able to get the CompetitorName that way. If I include it into the group by section, the "Customer" will show up multiple times. Otherwise I have to use an aggregate function to select which "CompetitorName" I want to use, but of course MAX doesnt work.

I'm sure this can be done somehow, but at the moment i've got no idea how.

Thanks in advance for any help.

Answer 1

select customer, competitorname, competitorcount
FROM
(
 select *, rn = ROW_NUMBER() over (
  partition by customer order by competitorcount desc)
 from tbl
) X
WHERE rn=1

If you want to show TIES at the Max value, change ROW_NUMBER() to RANK().

You might even find the cross apply version faster, but it doesn't work for ties. TOP 1 ensures a single record if there are ties among the competitors.

select C.*
FROM
(select distinct customer from tbl) A
cross apply
(select top 1 B.*
 from tbl B
 where A.customer = B.customer
 order by B.competitorcount desc) C

It first selects all the distinct customers. Then for each customer, it goes off and retrieves the records with the MAX(competitorcount) for that customer.

Answer 2

There are a couple of ways to do this, but the most straightforward way is:

WITH Custs AS (
   SELECT 
      Customer, 
      CompetitorName,
      CompetitorCount,
      ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY CompetitorCount DESC) AS __ROW
   From MyTable
)
SELECT
    Customer,
    CompetitorName,
    CompetitorCount
FROM Custs
WHERE __ROW = 1;

and if you want ties (where the highest counts tie for a customer, and you want to specify both rows), use RANK() instead of ROW_NUMBER() in the above query.

You could also do this using a self-join:

WITH Custs AS (
    SELECT Customer, MAX(CompetitorCount) AS CompetitorCount
    FROM MyTable 
    GROUP BY Customer)
SELECT m.Customer, m.CompetitorName, m.CompetitorCount
FROM MyTable m
INNER JOIN Custs c
   ON  c.CompetitorCount = m.CompetitorCount
   AND c.Customer        = m.Customer;