Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unanswered: generate all possible subsets from master set?

    Hi.

    I have a master set of values, lets say

    (1,2,3)

    for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.

    (1)
    (2)
    (3)
    (1,2)
    (1,3)
    (2,3)
    (1,2,3)

    thx.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can I ask why?

    In a table or a string?

    I'm thinking CROSS JOIN...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Back up a sec', manster.

    Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.

    But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.

    blindman

  4. #4
    Join Date
    Nov 2003
    Posts
    8
    thanks, all.

    Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."

    if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.

    thx.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That sounds bizzare!

    Can you post the DDL of the table?


    And some sample data?

    And are you're higher ups high?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Posts
    8
    yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.

    bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.

    table1

    F1 F2 F3 etc.


    output is all possible subsets of these fields:

    F1
    F2
    F3
    F1 F2
    F2 F3
    F1 F3
    F1 F2 F3

    thx!

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow

    You'll also need
    Code:
    CREATE TABLE Numbers(n int)
    GO
    DECLARE @x int
    SET NOCOUNT ON
    SELECT @x = 1
    WHILE @x < 101 BEGIN
    INSERT INTO Numbers (n) SELECT @x
    SELECT @x = @x + 1
    END
    SET NOCOUNT OFF
    To play with it...vary cool though...

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What kind of processing power are your higher-ups prepared to pay for?

    ...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
    Start it running, and then go get yourself a cup of coffee...in Brazil.

    blindman

  9. #9
    Join Date
    Nov 2003
    Posts
    8
    I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

    Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

    thx

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    What kind of processing power are your higher-ups prepared to pay for?

    ...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
    Start it running, and then go get yourself a cup of coffee...in Brazil.

    blindman
    LOL...

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646

    Some Calculations here...

    Say we have a table of 10,000 measly rows...

    You want to join col1 to each of the other 14 columns to represent permutations

    OK Col1 + Col2 = 100,000

    So i't like Col1 , Row 1 + Col2, Every row

    Do that 14 more times for all the other columns...1.4 million rows

    And we haven't yet begun! That's still just 2 dimensions of the data

    Now lets see, Three dimensions

    I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
    Then Col1, Row1, Col2, Row2, Col3 All the rows of data

    So what that would be 10,000 * 10,000 * 10,000

    Blindman help me out here, sound right?

    The for all other cols * 14, or 14 million?

    4 Dimensions...

    140,000,000? Just a guess...

    That about right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2003
    Posts
    8
    here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...

    mine is n=15, k=15

    and I'm looking for lex order, list of elements.


    http://www.theory.cs.uvic.ca/~cos/gen/comb.html

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by manster
    I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

    Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

    thx
    Was busy doing calcs when you posted...

    Are you trying to build a "pick list" of what fields you want to select for an end user..

    Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..

    Code:
    DECLARE @x BIGINT
    SELECT @x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
    SELECT @x
    I get an arithmetic overflow at 13...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2003
    Posts
    8
    I guess you could call it a pick list as long as all subsets are represented.

    I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.

    thx.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But that's only bit data...is that what you're looking for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2003
    Posts
    8
    yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •