December 3, 2013

FizzBuzz in One MySQL Statement

mysql

I was thinking recently that it would funny to implement FizzBuzz using only a single MySQL statement.  FizzBuzz is a simple test that a programmer might run into at a job interview.  They may be asked to implement FizzBuzz in a specific language or with pseudo-code, depending on the interview.  For whatever reason FizzBuzz tends to be a polarizing subject with some programmers finding it insulting, and others finding it amusing.

Programmers unfamiliar with the modulo operator will usually try to solve FizzBuzz using a division operator and then test whether the result is a whole number.  A surprising number of programmers will simply give up.  What makes FizzBuzz interesting to me is that it’s a quick problem with multiple solutions.  The particular solution a programmer chooses can help you assess their personal style of problem solving.

The logic part of a FizzBuzz solution in MySQL is almost exactly the same as a procedural language.  The challenge with a declarative language like SQL is that you can’t easily loop and generate 100 numbers.  Of course you can design a stored procedure which creates a table with 100 rows, but what fun would that be?  A greater challenge is to accomplish the Fizz Buzz output with one single MySQL statement and leave no trace of data behind.  In this case, a trick is using the UNION statement in combination with JOIN to generate the correct amount of fake data.  MySQL allows you to select a value without specifying a table name, for example “select 1” will return a single row.   The UNION effectively combines the single rows using addition and the JOIN multiplies them all together.  I chose to create three fake tables with 5 x 5 x 4 rows, which equals 100.  The same thing could be done with two fake tables of 10 x 10, or one fake table with 100 rows.

After playing around a bit and discovering the union trick online this is what I came up with:

-- FizzBuzz in MySQL
SELECT
  IF(counter % 3 = 0,
  IF(counter % 5 = 0,'Fizz Buzz','Fizz'),
  IF(counter % 5 = 0,'Buzz',counter)
) AS RESULT
FROM (
  -- union trick to select 100 fake rows (1 * 5 * 5 * 4 = 100)
  SELECT
    @i := @i + 1 AS counter
  FROM
    (SELECT @i := 0) AS dummy_1, -- (resets counter w/o affecting row count)
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) dummy_5_1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) dummy_5_2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) dummy_4
) AS numbers

Do you have an interesting way to run FizzBuzz?  Strong negative or positive feelings about FizzBuzz?  Feel free to leave me a comment below.

One Comment on “FizzBuzz in One MySQL Statement

Mark Lowery
December 12, 2014 at 6:20 am

Its funny I ran into that question using only javascript…

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *