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.

Microsoft SQL Server Enterprise Edition (64-bit), Version 10.50.1600 RTM — Webfarmer
– Webfarmer, Commented Jan 24, 2011 at 3:42

RichardTheKiwi · Accepted Answer · 2011-01-24 03:34:25Z

7

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.

edited Jan 24, 2011 at 3:34

answered Jan 24, 2011 at 3:20

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Webfarmer Over a year ago

This did the trick (except that in the inner query the tablename "[...] from customer" should be "[...] from MyTable". MS SSMS seem to have problem with the query (squiggly red lines all over the place) but the results are fine. Thanks!

Webfarmer Over a year ago

This would require 15 reputation, which i dont have yet :) I don't know what TIES is, can you explain that to me?

RichardTheKiwi Over a year ago

Ah. I didnt' know that; now you do :) I blitzed past that stage. What I mean by ties is that if Google and Bing both have 20 as their counts, using RANK shows both whereas ROW_NUMBER will only show one. The 2nd query, using CROSS JOIN, will always show one of them only.

Dave Markle · Accepted Answer · 2011-01-24 03:23:03Z

3

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;

edited Jan 24, 2011 at 3:23

answered Jan 24, 2011 at 3:17

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

6 Comments

VoodooChild Over a year ago

Is this a Common Table Expression?

Dave Markle Over a year ago

Yes, WITH Custs AS (...) section is a CTE.

VoodooChild Over a year ago

@Dave Markle: +1, could you explaing ROW_NUMBER() OVER (PARTITION BY Customer please in more detail? What is PARTITION BY and ROW_NUMBER() function?

Dave Markle Over a year ago

ROW_NUMBER() means "make a column of row numbers". PARTITION BY means "restart the numbering when the Customer field changes" and ORDER BY in this context means "perform the ordering starting with the highest CompetitorCount". They're referred to as "windowing functions" in T-SQL and were added in SQL Server 2005. Because of the partitioning and ordering parameters, you always know that the MyTable row where __ROW = 1 for any given customer will be the Customer row with the highest CompetitorCount.

Webfarmer Over a year ago

This doesn't work for me, MS SSMS returns "Invalid column name __ROW"

|

Collectives™ on Stack Overflow

T-SQL Group By Problem

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related