Three examples of SQL queries that reduce combinations in the Prime Lotto system

Structured Query Language or SQL is a popular tool for querying databases. Lottery results are in some ways the ultimate database: defined, structured and transparent. But it can be difficult to know where to start filtering results and combinations.

This article shows you three simple examples of requesting a lottery number combination file.

Using the Prime Lotto system as a number base

The prime lottery system uses simple probability to define a base of numbers to include in your combinations. In each line there are 2 primes, 1 non-primarily odd number and 3 even numbers. E.g:


3,7,9,4,18,38
13,29,27,10,18,40
11,23,25,6,16,32

You need the full combo file to process any SQL commands; there must be over 500,000 lines of numbers. You can import these into an MS Access database, or there are several Internet services that provide the data and access to queries for you.

We now have a starting point for reducing the size of our combinations using SQL commands using the following file structure.


6 balls per line: n1,n2,n3,n4,n5,n6

Primes: n1,n2
Non Prime odd: n3
Even numbers: n4,n5,n6

1. Limit even numbers to a specific number group

In this first query we want to make sure that the even numbers are distributed over all the 40 balls. There are four “deciles” between 1 and 40:

  • 1-10
  • 11-20
  • 21-30
  • 31-40

A simple strategy may be to ensure that none of the even numbers are in the same group. A good starting point is to first write the command in plain English:


"All even numbers must be spread across the full range of numbers."

Now it is a simple process to transform this statement into a SQL command. We can do this with a simple “where” query:


where
n4<11 and
(n5>10 and n5 <21)
and n6>20

When I ran this query through my own database, it returned an encouraging 118,800 combinations. More filtration can reduce the number even more. By limiting the last even number to be greater than 30, the query returned only 59,400 number of lines.

2. Ignoring prime numbers from the latest Lotto result

While some players believe that “hot” numbers exist, it may make sense to exclude any primes from the latest result. Here’s how SQL can do it:


Assuming the prime numbers 2 and 5 were in the previous result:

where
(n1<>2 and n1<>5)
and
(n2<>2 and n2<>5)

It reduces the combinations to 392,445, but the advantage is to add the first query:


(n1<>5 and n1<>2)
and (n2<>2 and n2<>5)

and
n4<11 and
(n5>10 and n5 <21)
and n6>30

The filtering by combining the two queries resulted in 40,500 combinations.

3. Limiting the sum of each Lotto line

One way to filter numbers is to look at the sum of each row of numbers. I did a rough review of the last 5 weeks of NZ Lotto and compiled the maximum and minimum totals.

Then I ran the following query:


where
(n1+n2+n3+n4+n5+n6)<130
and
(n1+n2+n3+n4+n5+n6)>100

This returned 235,438 combinations, but again comes the benefit of combining the previous queries. The number of lines returned by combining all three queries was a much reduced 21,942 lines.

summary

This article explained how to create three simple SQL queries in the primary lottery database. The type of query you can design is limited only by your own creativity and skill level and can have a significant impact on your lottery results.