Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: Finding all possible unique combinations of values


    Hello,
    I am looking for thoughts on how to find all possible combinations of values, for example:

    My table includes:
    Code:
    CREATE TABLE temp1 (item varchar(50), ORDER int);
    
    INSERT INTO temp1 (item, order) VALUES ('apple',1);
    INSERT INTO temp1 (item, order) VALUES ('pear',2);
    INSERT INTO temp1 (item, order) VALUES ('blueberry',3);


    I need the output to be like this:

    apple
    pear
    blueberry
    apple, pear
    apple, blueberry
    pear, blueberry
    apple, pear, blueberry

    I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.


    Would love some guidance!
    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Homework assignment?
    You need the output to be a comma-separated list?
    One way to do this would be to add a fourth item with null as item. A cross-join would then give all the permutations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    Not a homework assignment I only wish I was still in school!

    In the real data, I have 10 variables that I need to find all possible combinations.

    The end result is I will be creating segments of people that fit the criteria of the segment... but first step is to know how many segments I will end up with and how to actually create them.

    I've not ever worked with cross join before. I will research that a bit, but any add'l tips you could give would be great.

  4. #4
    Join Date
    Mar 2013
    Posts
    3
    I think I've got it, but not using cross join... however it may not be the most efficient.

    Code:
    select al.* from
    (select distinct a.item as item1, b.item as item2, c.item as item3
    from temp1 a
    left join
    (select distinct b.item
    from temp1 b) b
    on a.item <> b.item
    left join
    select disinct c.item
    from temp1 c) c
    on a.item <> c.item and
    b.item <> c.item
    where b.item <> '' -- blank row added
    ) al
    where item1 <>''
    order by item1, item2, item3

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A cross join is just a join "without joins", essentially matching each record in one data set with every record in the other dataset.
    For three variables, you'll need a three-way cross-join:
    Code:
    select	a.item as itema,
    	b.item as itemb,
    	c.item as itemc
    from	temp1 as a,
    	temp1 as b,
    	temp1 as c
    where	a.item < b.item
    	and b.item < c.item --Eliminates duplicated items
    ... but this will not give you combinations of just one or two items. To get that, at a fourth "phantom" item to your records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    I did this one earlier this year ..

    DROP TABLE Elements;

    CREATE TABLE Elements
    (e CHAR(1) NOT NULL PRIMARY KEY);

    INSERT INTO Elements
    VALUES ('a'),('b'),('c'),('d');

    Permutations:
    SELECT E1.e, E2.e, E3.e, E4.e
    FROM Elements AS E1, Elements AS E2, Elements AS E3
    WHERE E1.e < E2.e
    AND E2.e < E3.e
    AND E3.e < E4.e
    GROUP BY CUBE (E1.e, E2.e, E3.e, E4.e);

    --combinations;
    SELECT E1.e, E2.e, E3.e, E4.e
    FROM Elements AS E1, Elements AS E2,
    Elements AS E3, Elements AS E4
    WHERE E1.e NOT IN (E2.e, E3.e, E4.e)
    AND E2.e NOT IN (E3.e, E4.e)
    AND E3.e NOT IN (E4.e)
    GROUP BY CUBE (E1.e, E2.e, E3.e, E4.e)
    ORDER BY E1.e, E2.e, E3.e, E4.e;

Posting Permissions

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