CODEWITHSUNDEEP
mysqlsqlpermutation
Eric Hotinger
Eric Hotinger

Reputation: 9316

Finding Unique Tuple and Column Permutations

I have a table of numbers which consists of various nums from range [0-9]

 --------
 | nums |
 --------
 |  0   |
 |  1   |
 | .... |
 |  9   |
 --------

I want to take the cartesian product of this table multiple times (in fact, 8 times to be precise):

SELECT * FROM numbers A, numbers B, numbers C, numbers D, ...

Such that A.nums and B.nums, ... are unique combinations together AND they are not equal to the same value another number is.

Here's some sample output I would want to see:

(0, 1, 2, 3)
(3, 2, 1, 0)
(2, 1, 3, 0)
(0, 2, 1, 3)
(0, 9, 8, 7)
(1, 2, 3, 4)
(1, 3, 5, 9)
(1, 9, 8, 7)

... BUT NO TUPLES like (0, 0, 1, 2), (1, 1, 2, 2), etc. Tuples should also not be repeated, such as (1, 2, 3, 4) and (1, 2, 3, 4) Every value in the tuple must be different from another value in the tuple.

I'm sure there's a more efficient way of doing this, but I've resorted to A.nums != B.nums, ... for every single possibility. It seems relatively efficient; offering ~300 ms run-time for an 8-way cartesian product, but I would like to know if there's a simpler, more elegant solution with similar run-time.

Upvotes: 1

Views: 662

Answers (1)

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

CREATE TABLE nums (x INT);

INSERT INTO nums (x) VALUES (0), (1), (2), (3), (4); 

SELECT a.x, b.x, c.x, d.x 
FROM nums AS a 
JOIN nums AS b ON b.x NOT IN (a.x) 
JOIN nums AS c ON c.x NOT IN (a.x, b.x) 
JOIN nums AS d ON d.x NOT IN (a.x, b.x, c.x);

Upvotes: 2

Related Questions