T-SQL implementation of FizzBuzz

  • Eric M Russell

    SSC Guru

    Points: 125020

    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

  • gsc_dba

    SSCertifiable

    Points: 5407

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Never heard of that interview question before.

  • Velveeta22

    Ten Centuries

    Points: 1289

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

    WHERE n < 101

  • Eric M Russell

    SSC Guru

    Points: 125020

    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

  • WILLIAM MITCHELL

    SSChampion

    Points: 13651

    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

  • Bill Talada

    SSChampion

    Points: 11956

    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

  • austin.harrison

    SSC Journeyman

    Points: 80

    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

  • Bill Talada

    SSChampion

    Points: 11956

    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

  • Eric M Russell

    SSC Guru

    Points: 125020

    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

  • Bill Talada

    SSChampion

    Points: 11956

    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 11 (of 11 total)

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