CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designsql-server-2014-express
PeroProspero
PeroProspero

Reputation: 1

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?

Upvotes: -1

Views: 287

Answers (2)

Franc
Franc

Reputation: 1

With this table: id, tbl1, tbl2, tbl3, tbl4

and with this code, you can find the occurrences:

--alter table [BASE1].[dbo].[MiBase.2sep22] add idserie int identity(1,1)

--inicio all-- Declare @tblConteos TABLE(Veces int, Nros varchar(30), Orden varchar(10))

/* Conocer Parejas*/ ;WITH SEMILLA AS (Select [idserie] as Serie, Numero from [BASE1].[dbo].[MiBase.2sep22] UNPIVOT(Numero FOR Nros in (N1, N2, N3, N4, N5, N6)) as Num) , SERIES2 AS (Select TOP 100 PERCENT S1.Serie, S1.Numero as Numero1, S2.Numero as Numero2 FROM SEMILLA AS S1 CROSS JOIN SEMILLA AS S2 WHERE S2.Serie = S1.Serie AND S2.Numero <> S1.Numero AND S2.Numero > S1.Numero ORDER BY S1.Numero) insert into @tblConteos SELECT COUNT(DISTINCT(Serie)) AS Veces, Numero1 + ',' + Numero2 as Parejas, 'Parejas' FROM SERIES2 GROUP BY Numero1, Numero2 HAViNG COUNT(DISTINCT(Serie))>1

SELECT TOP 20 Veces, Nros FROM @tblConteos where orden = 'PAREJAS' ORDER BY Veces DESC

Upvotes: 0

Thom A
Thom A

Reputation: 95534

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;

Upvotes: 1

Related Questions