I have a query that returns all possible combinations from 5 columns. The problem is I want to remove duplicates (Car | Car) and reserve order duplicates (Truck | Car) if (Car | Truck) already exists. I’ve played around with DISTINCT, but haven’t had any luck.
Sample Data:
wdt_id|column1|column2|column3|column4|column5
1|Car|Car|''|Cat|''
2|Truck|Truck|''|Dog|''
3|Plane|Plane|''|Bird|''SELECT
item1, item2, item3, item4, item5
,concat_ws('<br>',NULLIF(item1,''),NULLIF(item2,''),NULLIF(item3,''),NULLIF(item4,''),NULLIF(item5,'')) as combination
FROM (
SELECT column1 AS item1
FROM sample_data
) AS t1
CROSS JOIN (
SELECT column2 AS item2
FROM sample_data
) AS t2
CROSS JOIN (
SELECT column3 AS item3
FROM sample_data
) AS t3
CROSS JOIN (
SELECT column4 AS item4
FROM sample_data
) AS t4
CROSS JOIN (
SELECT column5 AS item5
FROM sample_data
) AS t5
GROUP BY
item1, item2, item3, item4, item5
,concat_ws('<br>',IFNULL(item1,''),IFNULL(item2,''),IFNULL(item3,''),IFNULL(item4,''),IFNULL(item5,''))
SELECT
item1, item2, item3, item4, item5,
concat_ws('<br>', NULLIF(item1,''), NULLIF(item2,''), NULLIF(item3,''), NULLIF(item4,''), NULLIF(item5,'')) as combination
FROM (
SELECT column1 AS item1
FROM sample_data
) AS t1
CROSS JOIN (
SELECT column2 AS item2
FROM sample_data
) AS t2
CROSS JOIN (
SELECT column3 AS item3
FROM sample_data
) AS t3
CROSS JOIN (
SELECT column4 AS item4
FROM sample_data
) AS t4
CROSS JOIN (
SELECT column5 AS item5
FROM sample_data
) AS t5
WHERE
item1 != item2 AND item1 != item3 AND item1 != item4 AND item1 != item5
AND item2 != item3 AND item2 != item4 AND item2 != item5
AND item3 != item4 AND item3 != item5
AND item4 != item5
AND item1 < item2 AND item1 < item3 AND item1 < item4 AND item1 < item5
AND item2 < item3 AND item2 < item4 AND item2 < item5
AND item3 < item4 AND item3 < item5
AND item4 < item5
GROUP BY
item1, item2, item3, item4, item5,
concat_ws('<br>', IFNULL(item1,''), IFNULL(item2,''), IFNULL(item3,''), IFNULL(item4,''), IFNULL(item5,''))