Select value of largest groups / most common values in bridge table in T-SQL

Question

I have a table which represents a "Contract" between two rows in another table. Given the data below, how can I get the most common Distributer for each EndUser?

Contracts

EndUserId | DistributerId | StartDate | EndDate 
-----------------------------------------
1         | 8             | ...       | ...
1         | 9             | ...       | ...
1         | 9             | ...       | ...
2         | 8             | ...       | ...
2         | 8             | ...       | ...
2         | 9             | ...       | ...   
3         | 8             | ...       | ...   
3         | 9             | ...       | ...

The query I'm after must return the following:

EndUserId | DistributerId
-------------------------
1         | 9
2         | 8
3         | 8 or 9, it is of no consequence.

Thanks in advance! Searching hasn't been much help here because it's hard to describe the goal without sample data etc.

score 3 · Accepted Answer · edited Nov 07 '11 at 09:00

3

Untested, but I think this will do it:

WITH ContractCounts AS 
( --First Get the counts for each distributer
  SELECT EndUserID, DistributerID, Count(*) As ContractCount
  FROM Contracts
  GROUP BY EndUserID, DistributerID
),
ContractMax AS 
(  -- Then find out how many contracts the largest distributed for each user had
  SELECT EndUserID, Max(ContractCount) As MaxContractCount
  FROM ContractCounts 
  GROUP BY EndUserID
)
-- and finally select only the distributor for each user who's count matches the prior query
SELECT c.EndUserID, MAX(c.DistributerID) AS DistributerID
FROM ContractCounts c
INNER JOIN ContractMax m on m.EndUserID=c.EndUserID AND c.ContractCount = m.MaxContractCount
GROUP BY c.EndUserID

edited Nov 07 '11 at 09:00

Brock Adams

90,639
22
233
295

answered Nov 07 '11 at 03:57

Joel Coehoorn

399,467
113
570
794

Hey mate, that is perfect except that for EndUserId 3 in my example it will return two rows, one with 8 and one with 9. I submitted an edit that adds a GROUP BY and MAX which simply grabs one of the values. Thanks! – andrej351 Nov 07 '11 at 04:29
2

@andrej351, your suggested edit was rejected by users (who I suspect did not analyze the question & answer, nor note that you were the OP). ... Since, the change was what made the answer work for you -- the question asker -- I edited it back in for you. Joel Coehoorn can revert if he doesn't like the change. – Brock Adams Nov 07 '11 at 09:03

score 1 · Answer 2 · answered Nov 07 '11 at 04:00

1

select *
from
(
    select *, row_number() over(partition by EndUser order by UserDistCount desc) as rownum
    from
    (
        select EndUserId, DistributorId, count(*) as UserDistCount
        from Contracts
        group by EndUserId, DistributorId
    ) a
) b
where rownum = 1

answered Nov 07 '11 at 04:00

I prefer this solution, as: 1) it solves the "return 1 value arbitrarily in the case of ties" issue inherently; and 2) it can be generalizable for other applications to "return the top 'n' entries" by changing rownum = 1 to rownum <= n – jhchou Nov 05 '17 at 09:52

Select value of largest groups / most common values in bridge table in T-SQL

2 Answers2