[SOLVED] Check pairs of lottery numbers from archive

Check pairs of lottery numbers from archive

I'm trying to find best possible solution to check pairs concerning to my national lottery. They draw six numbers. I have a database with all the previous drawings. The question is what is the most efficient table structure and the way of checking lottery number pairs'?

This is how my table looks:

year (float)    
date (nvarchar)    
draw (float)    
no1 (smallint)    
no2 (smallint)    
no3 (smallint)    
no4 (smallint)    
no5 (smallint)    
no6 (smallint)

I use this query to find a pair of numbers:

SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '36' IN (no1,no2,no3,no4,no5,no6)

I got the result 10 (the pair was draw out 10 times).

If I want to check the next pair, for example 1 and 37, I need to use the query again but with other numbers

SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '37' IN (no1,no2,no3,no4,no5,no6)

I got the result 13 (the pair was draw out 13 times)

Is there a SQL query (or some better solution) that can automatically check pairs in the number range from 1 to 90; example:

Pair 1 and 2
Pair 1 and 3
Pair 1 and 4
.
.

all the way up to pair 89 and 90?

Solution

Reading a little through the lines a little here, but a Tally seems to be the best choice here:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (90) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2)
SELECT COUNT(*) AS Draws,
       T1.I AS Number1,
       T2.I AS Number2
FROM dbo.Archive S
     CROSS JOIN Tally T1
     JOIN Tally T2 ON T1.I < T2.I
WHERE T1.I IN (no1,no2,no3,no4,no5,no6)
  AND T2.I IN (no1,no2,no3,no4,no5,no6)
GROUP BY T1.I, T2.I;