Distinct combination selection

Distinct combination selection

I have a need to determine the distinct number of combinations of selections in a fantasy competition – where the competition is to guess the final ladder of a sporting league. Participants order their selection based on who they think will come first, second, third, etc.

It got me thinking… how can I get a distinct count of records grouped by PlayerID with multiple rows? Picture the following:
20150617_ladder_1
You can easily see here that PlayerID 100001 and 100173 have the exact same selection. Assuming there are hundreds of entries (which I’ve omitted on purpose), I want to return a list of selections and how many players have picked that selection.

There are many ways to skin a cat, but making use of STUFF and FOR XML I can generate a concatenated list of selections and then perform a simple count from there. For the purposes of this example, I am going to divide the query up into pieces with #temp tables, though it could easily be returned with one sub-query.

First, collect the players and concatenate their selections making sure to order by position so they’re populated in the correct order of selection.

SELECT p1.PlayerID,
      STUFF( (SELECT ','+Team
              FROM ladder p2
              WHERE p2.PlayerID = p1.PlayerID
              ORDER BY Position
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
           ,1,1,'')
      AS Selection
INTO #selections
FROM ladder p1 
GROUP BY PlayerID

SELECT * FROM #selections

This will return a result set like the following:

20150617_ladder_2

Which we can now easily query the counts of distinct selection. Like so:

SELECT Selection, COUNT(Selection) thecount
FROM #selections
GROUP BY Selection
HAVING (COUNT(Selection) >= 1) 
 -- change to >1 if you don't care about unique selection
ORDER BY COUNT(Selection) DESC

Delivering the desired result like the following:

20150617_ladder_3

I trust this assists someone and, if time permitted, gets me thinking about other ways to achieve the same outcome.

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
Leave a comment

Your email address will not be published. Required fields are marked *