Best way to store list of numbers and to retrieve them

Question

Hi. What is the best way to store a list of random numbers (like lotto/bingo numbers) and retrieve them? I'd like to store on a Database a number of rows, where each row contains 5-10 numbers ranging from 0 to 90. I will store a big number of those rows. What I'd like to be able is to retrieve the rows that have at least X number in common to a newly generated row.

Example:

[3,4,33,67,85,99]
[55,56,77,89,98,99]
[3,4,23,47,85,91]

Those are on the DB

I will generate this: [1,2,11,45,47,88] and now I want to get the rows that have at least 1 number in common with this one.

The easiest (and dumbest?) way is to make 6 select and check for similar results.

I thought to store numbers with a large binary string like 000000000000000000000100000000010010110000000000000000000000000 with 99 numbers where each number represent a number from 1 to 99, so if I have 1 at the 44th position, it means that I have 44 on that row. This method is probably shifting the difficult tasks to the Db but it's again not very smart.

Any suggestion?

Answer 1

You should create a table like so:

TicketId Number
1        3
1        4
1        33
1        67
1        85
1        99
2        55
2        56
2        77
etc...

Then your query, at least for X = 1, becomes:

SELECT DISTINCT TicketId FROM Ticket WHERE Number IN (1, 2, 11, 45, 47, 88)

The advantage of this is that you can use an index instead of a full table scan.

For X greater than one, you could do the following:

SELECT TicketId, COUNT(*) FROM (
    SELECT TicketId FROM Ticket WHERE Number = 1
    UNION ALL
    SELECT TicketId FROM Ticket WHERE Number = 2
    UNION ALL
    SELECT TicketId FROM Ticket WHERE Number = 11
    UNION ALL
    SELECT TicketId FROM Ticket WHERE Number = 45
    UNION ALL
    SELECT TicketId FROM Ticket WHERE Number = 47
    UNION ALL
    SELECT TicketId FROM Ticket WHERE Number = 88
) AS T1
GROUP BY TicketId
HAVING COUNT(*) >= 3

Again this will be able to use the index.