Arian Gerryts

Greenhorn

Posts: 11

posted 5 years ago

Hi there

I am experiencing some difficulty comparing entries of lotto numbers in a database with an existing winning number.

I stored all the numbers in individual columns as strings and I am supposed to retrieve the numbers according to matches. Three matches, four matches, five matches etc.

I was thinking along the lines of doing the following

but this is tricky as a set of 7 numbers can produce many different matches of three for example. I have been sitting with this problem for a while.

Any advice perhaps on the best why top approach this as retrieving all the numbers from the database and then comparing each digit with the digits of the winning lotto numbers I find also a bit complicating.

Kind regards

I am experiencing some difficulty comparing entries of lotto numbers in a database with an existing winning number.

I stored all the numbers in individual columns as strings and I am supposed to retrieve the numbers according to matches. Three matches, four matches, five matches etc.

I was thinking along the lines of doing the following

but this is tricky as a set of 7 numbers can produce many different matches of three for example. I have been sitting with this problem for a while.

Any advice perhaps on the best why top approach this as retrieving all the numbers from the database and then comparing each digit with the digits of the winning lotto numbers I find also a bit complicating.

Kind regards

posted 5 years ago

One option I can think of is:

Firstly, store the numbers as numbers only (instead of String).

Then, select all the records, sort all the records' columns in ascending order. Also sort the winning number in ascending order. This will make comparison easier.

After this, you can easily calculate number of matches.

Advantage : Its easy to match.

Disadvantage : You'll have to keep all data of table in memory, which will increase the footprint of code (or you'll have to go on iterating over db with first 1000 rows, then second 1000 rows and so on). Even after loading the data, you'll have to sort all the rows, and you'll waste processor's time during sorting the rows which do not match at all.

Second option is:

While storing the numbers in DB, sort it before storing itself. If order doesn't matter for you, then store the data such that column1 <= column2 <= column3 and so on. If order does matter, then you can have extra column which stores the order (say 543216 means the original number is 5th column, followed by 4th column and so on).

Advantage : You can get low number of rows while forming the query itself (because now you know that smallest value should be searched in column 1 and so on). Also, you'll sort the rows one by one while insertion, so while selecting, you don't have to sort them again and you can directly go on comparing.

Disadvantage : 'The last column for ordering' thing can increase time complexity.

However, no matter what approach you follow, use 'OR' condition in query. 'AND' condition would return rows only if all the numbers are matching.

I hope this helps.

Firstly, store the numbers as numbers only (instead of String).

Then, select all the records, sort all the records' columns in ascending order. Also sort the winning number in ascending order. This will make comparison easier.

After this, you can easily calculate number of matches.

Advantage : Its easy to match.

Disadvantage : You'll have to keep all data of table in memory, which will increase the footprint of code (or you'll have to go on iterating over db with first 1000 rows, then second 1000 rows and so on). Even after loading the data, you'll have to sort all the rows, and you'll waste processor's time during sorting the rows which do not match at all.

Second option is:

While storing the numbers in DB, sort it before storing itself. If order doesn't matter for you, then store the data such that column1 <= column2 <= column3 and so on. If order does matter, then you can have extra column which stores the order (say 543216 means the original number is 5th column, followed by 4th column and so on).

Advantage : You can get low number of rows while forming the query itself (because now you know that smallest value should be searched in column 1 and so on). Also, you'll sort the rows one by one while insertion, so while selecting, you don't have to sort them again and you can directly go on comparing.

Disadvantage : 'The last column for ordering' thing can increase time complexity.

However, no matter what approach you follow, use 'OR' condition in query. 'AND' condition would return rows only if all the numbers are matching.

I hope this helps.

Regards,

Anayonkar Shivalkar (SCJP, SCWCD, OCMJD, OCEEJBD)