Project Euler with T-SQL

May 26

Me and colleague in my current Project Tomas Eriksson  decided to start to try the Project Euler problemes in Microsoft SQL Servers  Language Transact SQL (T-SQL).
First problem was:
Add all the natural numbers below one thousand that are multiples of 3 or 5.
I made my solution hack back in 2008. It has some flaws. But I post it here to invite criticism. ( Yes I know I should have used % for mod instead of floor, yes I know while loops in SQL do not earn respect)
— Euler 1. Add all the natural numbers below one thousand that are multiples of 3 or 5.

declare @NumSerie table ( NatNum int )
declare @i int
select @i=1
While  (@i<1000)
Begin
    insert into @NumSerie (NatNum)
    select @i
    select @i=1+@i
End
select sum(NatNum) from @NumSerie
where NatNum*1.0/3-floor(NatNum*1.0/3)=0
or
NatNum*1.0/5-floor(NatNum*1.0/5)=0

Tomas solution is much more elegant although it does not work everywhere. But it works on the big and complex Enterprise database where he resides and the sys.objects has enough tuples to impress.

USE
tempdb;GO
WITH
int_CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY object_id)
        AS number FROM sys.objects)
SELECT SUM(number)FROM int_CTE WHERE
number < 1000 AND (number % 5 = 0 OR number % 3 = 0);

2 comments

  1. Aaron West /

    Just change FROM sys.objects
    to FROM sys.objects o1, sys.objects o2

    or sys.objects CROSS JOIN sys.objects

    A cross-product join of two or three copies of the table is plenty for 1000 numbers.

  2. Ben /

    — 5 + 10 + 15 + … + 995
    — =
    — 5 * (1 + 2 + 3 + … + 199)
    — =
    — 5 * (199 * 200) / 2
    SELECT Answer =
    — sum of first n numbers = (n*n+1)/2
    5 * (199 * 200) / 2
    +
    3 * (333 * 334) / 2

    15 * ( 66 * 67) / 2

Leave a Reply

Your email address will not be published.