The road to using Mysql

Hi, I've recently come across the Euler Project The Euler Project that sets mathematical challenges in whatever coding language you prefer to use. Now I'm not claiming that Mysql is particularly apt for these challenges, but I wondered if I could possibly solve one or two merely by the use of Mysql (not using any other scripting language such as Php or RoR).

Here's the first question:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

First I'm going to see a way it can be done in Perl (the following is adapted from a solution I found at Euler oneliners in Perl ) and then see how I can apply this in Mysql:

Code:

For my Mysql code I will start by seeing how we test if a number is divisible by another number in Mysql. One way is to use the function MOD that returns the exact remainder after division. It should therefore return 0 when the number is divisible by your test figure. So select mod(9,3); returns 0 whereas select mod(8,2); returns 2.
Next we need a way to iterate up to 999 (ie all numbers below 1000). I tend to use a dummy table populated with an ascending count for this type of thing; however it's possible to use a variable technique so long as you join to a table with enough rows in it. For example, if we have a table orders with 20,000 records, we can bring back just 20 row-numbered records like this:

Code:

So to bring back all those where the row_num is divisible by 3 we put the above into an inner select:

Code:

From there it's an easy step to add in a [strong]OR[/strong] clause to include numbers divisible by 5, and to sum the result.

Code:

This gives an answer of: 233168. It came back in 0.08 of a second too, so wasn't too inefficient! I hope to look at another Euler teaser soon!