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 in filtering results and combinations.
This article will show you three simple examples of querying a combination file of lottery numbers.
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 prime odd number and 3 even numbers. For example:
You’ll need the full combination file to process any SQL commands; there should 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 query access for you.
We now have a starting point to reduce the size of our combinations using SQL commands using the following file structure.
6 balls per line: n1,n2,n3,n4,n5,n6
Non Prime odd: n3
Even numbers: n4,n5,n6
1. Restrict Even Numbers To A Specific Number Group
In this first query, we want to ensure the even numbers are spread out over all the 40 balls. There are four “deciles” between 1 and 40:
A simple strategy might be to ensure none of the even numbers are in the same group. A good starting point is to write out the command in plain English first:
"All even numbers must be spread across the full range of numbers."
Now, it’s a simple process to turn this statement into a SQL command. We can do this with a simple “where” query:
(n5>10 and n5 <21)
When I ran this query through my own database it returned an encouraging 118,800 combinations. More filtering can reduce the numbers even more. By restricting the last even number to be greater than 30 the query returned just 59,400 lines of numbers.
2. Ignoring Prime Numbers From The Most Recent Lotto Result
While some players believe “hot” numbers exist it might make sense to exclude any primes from the most recent result. Here’s how SQL can do that:
Assuming the prime numbers 2 and 5 were in the previous result:
(n1<>2 and n1<>5)
(n2<>2 and n2<>5)
That reduces the combinations to 392,445 but the benefit comes from adding the first query:
(n1<>5 and n1<>2)
and (n2<>2 and n2<>5)
(n5>10 and n5 <21)
The filtering by combining the two queries resulted in 40,500 combinations.
3. Restricting The Sum Of Each Lotto Line
One way of filtering numbers is to look at the sum of each line of numbers. I did a rough review of the last 5 weeks of NZ Lotto and worked out the maximum and minimum totals.
Then I ran the following query:
This returned 235,438 combinations but again the benefit comes from combining the previous queries. The number of lines returned by combining all three queries was a much reduced 21,942 lines.
This article explained how to create three simple SQL queries on the prime lotto database. The type of query you can design is only limited by your own creativity and skill level and might impact significantly on your lottery results.