How to select unique records from a 5 column table where the values can be the same but in different-CodePudding

I used 1 table to do a cross join 4 times giving me 5 columns of data. I used the same column in all 4 cross joins to get every combination. The only problem is I get the same data it is in a different order. I am trying to only get records that are unique.

I can only have one iteration of each set of data, ie 12345 - 12346 - 12347, but not 54321 - 64321 - 74321 or any combo where all the same numbers are combined. I am stumped.

Table 1 / column 1
1
2
3
4
5
...
100

Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t 
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
    t.col1 != t1.col1 and t.col1 != t2.col1 and t.col1 != t3.col1 and t.col1 != t4.col1 
    and t1.col1 != t2.col1 and t1.col1 != t3.col1 and t1.col1 != t4.col1 
    and t2.col1 != t3.col1 and t2.col1 != t4.col1
    and t3.col1 != t4.col1

col1	col2	col3	col4	col5
1	2	3	4	5
2	1	3	4	5
3	2	1	4	5
5	2	4	3	1
5	4	3	2	1

Only one of those works, the first one, because all the rest are just combinations of the first. Think of this with the original table having hundreds of rows.

CodePudding user response：

Replace all =! with <:

Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t 
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
    t.col1 < t1.col1 and t.col1 < t2.col1 and t.col1 < t3.col1 and t.col1 < t4.col1 
    and t1.col1 < t2.col1 and t1.col1 < t3.col1 and t1.col1 < t4.col1 
    and t2.col1 < t3.col1 and t2.col1 < t4.col1
    and t3.col1 < t4.col1

The problem with =! is while you prevent numbers x and y from being the same, you get two joins; one for the case where x < y and one for the case where x > y, leading to unwanted duplicate joins.