SQL Server

SQL – Distinct combination of selections

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.

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:

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.

Share on Google Plus

Mike250

Australian living. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf GTI. SQL Server Developer. Three sons. One daughter.

Leave a comment

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