Help finding all possible combinations within a group of data

Question

Help finding all possible combinations within a group of data

Viewing 5 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

J Livingston SQL (2/6/2012)
I think there's a small issue with the math. Using factorial would work for permutations (i.e. the orders of the colors matter), but is not right for combinations. So if the order in which the colors are specified is NOT important,
To know all non-repeating combinations of length d withing a distinct population of n you're looking for n!/((n-d)!d!)
To know all non-repeating combinations of all lengths within a distinct population of n you're looking for (2^n)-1
So for four colors it is (2^4) - 1 -= 15, 5 flavors yields (2^5)-1 = 31, so total combinations would be:
2*15*3*31 = 2790
Thanks Sean...appreciated and hopefully I will now remember the difference between combs and perms (maybe )

Actually the correction to both of us came from Matt. I too will try to remember the difference, but I suspect I will forget it again before I need to use that knowledge.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286602 More actions · Answer 2

mmiller 85218 (2/6/2012)
Sean Lange (2/6/2012)
Pretty sure you can handle the counts now.
As to getting the resultset of all possibilities...
What do you want that to look like? I know it was just assigned to you but I don't see anything that could be practical from having a list of combinations like this. I also don't begin to know how to pull that, which is probably why I keep asking about the practicality of it.
I'm not sure what I want it to look like! Its a crazy request and I don't see the point, but I won't win if I argue. Thanks for all of your help, and if any ideas come to mind, feel free to share, and if anything comes to mine, I will post my thoughts!

I understand you there but without knowing what you want it is not possible to help put something together. Perhaps you can go back and let "them" know that you can get the count of possibilities but what should the table of possibilities look like. You could explain there are a LOT of rows and you want to make sure it is a format that is most helpful for their task. They may then see how silly this request really is. At the very least they should be able to answer what format they want it in.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Cowne One Orange Chip Points: 26980 More actions · Answer 3

I think this gives you all of the combinations you're after, not sure how useful it would be as there's lots of them

WITH CTE(CatID,FieldID,Combinations,OptionsUsed) AS (

SELECT CatID,FieldID,CAST(FieldID AS VARCHAR(1000)),1

FROM #Cat

WHERE FieldID=1

UNION ALL

SELECT a.CatID,a.FieldID,

CAST(t.Combinations + '/' + CAST(a.FieldID AS VARCHAR(10)) AS VARCHAR(1000)),

CASE WHEN a.CatID=t.CatID THEN t.OptionsUsed+1 ELSE 1 END

FROM #Cat a

INNER JOIN CTE t ON a.FieldID>t.FieldID

AND (a.CatID=t.CatID+1 OR (a.CatID=t.CatID AND a.OptionsAvail>t.OptionsUsed))

)

SELECT Combinations

FROM CTE

ORDER BY Combinations;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

mmiller 85218 SSC Enthusiast Points: 141 More actions · Answer 4

This works great, but is only accounting for those with FieldID =1. Would you just double the code and change your WHERE statement to FieldID = 2?

Mark Cowne One Orange Chip Points: 26980 More actions · Answer 5

mmiller 85218 (2/7/2012)
This works great, but is only accounting for those with FieldID =1. Would you just double the code and change your WHERE statement to FieldID = 2?

It's just a matter of changing

WHERE FieldID=1

Not sure what you want here, maybe

WHERE CatID=1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Help finding all possible combinations within a group of data

Cookies on SQLServerCentral