Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trouble with logic on how to query for all possible 5 number combinations of numbers 1 - 69 into SQL table

EDIT: Just to clarify, the order of the results matters only in that 1,2,3,4,5 and 5,4,3,2,1 should be the same record. So I'm looking for distinct combinations.

I'm looking to build a SQL database with all possible 5 number combinations of numbers 1 - 69 where the same number cannot be used twice in a single 5 number combination. The result will contain 11,238,513 records. I'm having trouble writing the code to get this to work correctly with all logic at play. I've attempted a crazy macro in Excel VBA but I feel like SQL would have something simpler and quicker. I've asked something similar but it also just doesn't get me there (How do I create a list of 5 number permutations based on a list of numbers ranging from 1-69?).

I've tried variations of below, but I just can't seem to conceptualize how I should be attacking this. Any help?

WITH range
AS (
SELECT num1 AS c FROM TEST1
  UNION  
  SELECT num2 FROM TEST1
  UNION  
  SELECT num3 FROM TEST1
  UNION  
  SELECT num4 FROM TEST1
  UNION  
  SELECT num5 FROM TEST1)
SELECT *
FROM range r1,range r2,range r3,range r4,range r5
where r1.c <> r2.c and r1.c<>r3.c and r1.c<>r4.c and r1.c<>r5.c
and r2.c <> r3.c and r2.c<>r4.c and r2.c<>r5.c
and r3.c<>r4.c and r3.c<>r5.c
and r4.c <> r5.c
order by r1.c, r2.c, r3.c, r4.c, r5.c
like image 818
plankton Avatar asked Sep 01 '25 17:09

plankton


1 Answers

Cartesian product the table to itself 5 times, and choose only rows where the numbers are in increasing order (this ensures the same number isn't selected more than once, and that there are no permutations of the same 5 numbers):

SELECT r1.c, r2.c, r3.c, r4.c, r5.c
FROM range r1, range r2, range r3, range r4, range r5
WHERE r1.c<r2.c AND r2.c<r3.c AND r3.c<r4.c AND r4.c<r5.c
like image 55
gen-y-s Avatar answered Sep 04 '25 09:09

gen-y-s