mysql - Grouping with no repeating values in multiple columns -

i have following data:

+-----+-----+ | id1 | id2 | +-----+-----+ | 100 | aaa | | 100 | bbb | | 200 | aaa | | 200 | bbb | | 300 | aaa | | 300 | bbb | | 300 | ccc | | 400 | bbb | +-----+-----+ 

i need somehow group rows in table above , get:

+-----+-----+      +-----+-----+      +-----+-----+ | id1 | id2 |      | id1 | id2 |      | id1 | id2 | +-----+-----+      +-----+-----+      +-----+-----+ | 100 | aaa |  or  | 100 | bbb |  or  | 400 | bbb | acceptable | 200 | bbb |      | 200 | aaa |      | 300 | ccc | | 300 | ccc |      | 300 | ccc |      | 200 | aaa | +-----+-----+      +-----+-----+      +-----+-----+ 

which of above given grouped result-set not important long there no repeating occurrences of both id1 , id2 values. i.e. following result-set (got simple grouping id1 or id2) "wrong":

+-----+-----+       +-----+-----+ | id1 | id2 |       | id1 | id2 | +-----+-----+       +-----+-----+ | 100 | aaa |  ,  | 100 | aaa | both "wrong" | 200 | aaa |       | 100 | bbb | | 300 | aaa |       | 300 | ccc | | 400 | bbb |       +-----+-----+ +-----+-----+ 

not forget important fact. bound using mysql functions.

here's 1 idea - i'm not (my)sql best tool combinatorial problems...

 drop table if exists my_table;   create table my_table  (id1 int not null,id2 char(3) not null,primary key(id1,id2));   insert my_table values  (100 ,'aaa'),  (100 ,'bbb'),  (200 ,'aaa'),  (200 ,'bbb'),  (300 ,'aaa'),  (300 ,'bbb'),  (300 ,'ccc'),  (400 ,'bbb');   select *    my_table x     join my_table y       on y.id1 > x.id1      , y.id2 <> x.id2     join my_table z       on z.id1 > y.id1      , z.id2 <> x.id2      , z.id2 <> y.id2    order       rand() limit 1;  +-----+-----+-----+-----+-----+-----+  | id1 | id2 | id1 | id2 | id1 | id2 |  +-----+-----+-----+-----+-----+-----+  | 200 | aaa | 300 | ccc | 400 | bbb |  +-----+-----+-----+-----+-----+-----+ 

btw, if matters, can reformatted follows:

 select a.*    my_table    join        ( select x.id1 x_id1              , x.id2 x_id2              , y.id1 y_id1              , y.id2 y_id2              , z.id1 z_id1              , z.id2 z_id2           my_table x            join my_table y              on y.id1 > x.id1             , y.id2 <> x.id2            join my_table z              on z.id1 > y.id1             , z.id2 <> x.id2             , z.id2 <> y.id2           order              rand() limit 1       ) b      on (a.id1,a.id2) in((x_id1,x_id2),(y_id1,y_id2),(z_id1,z_id2)); 

(e.g.:)

+-----+-----+ | id1 | id2 | +-----+-----+ | 100 | aaa | | 200 | bbb | | 300 | ccc | +-----+-----+