T-SQL implementation of FizzBuzz

  • Comments posted to this topic are about the item T-SQL implementation of FizzBuzz

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Nice solution.

    One less line of code: 🙂

    SELECT

    CASE WHEN [x].[n] % 15 = 0 THEN 'FizzBuzz'

    WHEN [x].[n] % 3 = 0 THEN 'Fizz'

    WHEN [x].[n] % 5 = 0 THEN 'Buzz'

    ELSE CAST([x].[n] AS VARCHAR(9))

    END AS [p]

    FROM

    (

    SELECT

    ROW_NUMBER() OVER ( ORDER BY [id] ) AS [n]

    FROM

    [sys].[sysobjects]

    ) [x]

    WHERE

    [x].[n] <= 100

    ORDER BY

    [x].[n];

    gsc_dba

  • Never heard of that interview question before.

  • If you are going for fewest number of characters in the exercise you can save one more with:

    WHERE n < 101

  • Iwas Bornready (12/28/2016)


    Never heard of that interview question before.

    I've never heard it or asked it during an actual interview. Someone else on the team had suggested using it once for interviews, but when I saw their baseline solution was a cursor with 30 lines of code, I wanted something better, so I came up with this.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • One more version, using a "single" select statement 😉

    SELECT TOP 100

    CASE

    WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 15 = 0 THEN 'FizzBuzz'

    WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 3 = 0 THEN 'Fizz'

    WHEN ROW_NUMBER() OVER (ORDER BY object_id) % 5 = 0 THEN 'Buzz'

    ELSE CAST( ROW_NUMBER() OVER (ORDER BY object_id) AS varchar)

    END

    FROM

    sys.objects

  • In an empty database I only got 97 numbers from sys.objects.

    WITH x(y) AS (SELECT top (100) ROW_NUMBER() OVER (ORDER BY id) FROM sys.syscolumns s)

    SELECT CASE WHEN y % 15 = 0 THEN 'FizzBuzz' WHEN y % 3 = 0 THEN 'Fizz' WHEN y % 5 = 0 THEN 'Buzz' ELSE CAST(y AS varchar) END

    FROM x

  • Non set-based solution.

    Doesn't depend on selecting from a system table that has the desired number of rows. (What if it was for numbers 1-1000000?)

    Preserves the integer datatype of the numbers. (If that is needed.)

    DECLARE @Count INT = 0

    WHILE @Count < 100

    BEGIN

    SET @Count = @Count + 1

    IF (@Count%15 = 0)

    PRINT 'FizzBuzz'

    ELSE IF (@Count%3 = 0)

    PRINT 'Fizz'

    ELSE IF (@Count%5=0)

    PRINT 'Buzz'

    ELSE

    PRINT @Count

    END

  • Simple. Just add a few cross joins for however many you need.

    WITH x(y) AS (SELECT top (1000) ROW_NUMBER() OVER (ORDER BY s.object_id) FROM sys.objects s CROSS JOIN sys.objects t)

    SELECT CASE WHEN y % 15 = 0 THEN 'FizzBuzz' WHEN y % 3 = 0 THEN 'Fizz' WHEN y % 5 = 0 THEN 'Buzz' ELSE CAST(y AS varchar) END

    FROM x

  • Similar in concept to Oracle [DUAL] table, it would be useful if SQL Server had a system tally table, perhaps containing only a single INT column with rows from -2,147,483,648 to 2,147,483,647. Currently, a lot of debate is centered around how best to implement such a thing on the fly.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I currently use a table function to generate a range of numbers for joining to queries. I find it is easy for others to understand and is fast enough for everything we do. It includes an offset parameter to generate any range you may need. The code is below.

    Since sql server 2012 introduced Sequence objects which are brilliant by the way, I've been wondering if a Sequence may be the way to go.

    -- return range of rows for ss2012 or higher

    IF OBJECT_ID('dbo.GetOneMillionNumbers', 'IF') IS NOT NULL DROP FUNCTION dbo.GetOneMillionNumbers;

    GO

    CREATE FUNCTION dbo.GetOneMillionNumbers(@count AS int, @offset int)

    RETURNS TABLE

    AS

    RETURN

    (

    WITH x (y)

    AS

    (

    SELECT n1.na

    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (na)

    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n2 (na)

    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n3 (na)

    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n4 (na)

    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n5 (na)

    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n6 (na)

    )

    SELECT top (@count)

    ROW_NUMBER() OVER(ORDER by (SELECT NULL)) + @offset AS MySequence

    from x

    );

    GO

    -- SELECT * FROM dbo.GetOneMillionNumbers(775, 1000);

    -- SELECT max(MySequence) FROM dbo.GetOneMillionNumbers(1000000, -1000001);

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply