- February 25, 2015

FizzBuzz is a classic programming problem / interview question. In this post we'll solve FizzBuzz in SQL using a row generator.

The FizzBuzz problem:

- Write a program that prints the integers from 1 to 100.
- For multiples of 3 print "Fizz" instead of the number,
- For multiples of 5 print "Buzz“ instead of the number,
- For numbers which are multiples of both 3 and 5, print "FizzBuzz".

If I have a table my_numbers with the numbers from 1 to 100 in it, then this is trivial to do in SQL:

select case when mod(the_number,15)=0 then 'FizzBuzz' when mod(the_number,3)=0 then 'Fizz' when mod(the_number,5)=0 then 'Buzz' else to_char(the_number) end as "FizzBuzz!" from my_numbers order by the_number;

FizzBuzz! ---------------------------------------- 1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fizz 13 14 FizzBuzz ... 97 98 Fizz Buzz 100 rows selected.

But, how to get that table of the natural numbers from 1-100?

Well, I could create a table and then populate it using PL/SQL; or I could do this:

--10 rows select 1 as num from dual union all select 2 as num from dual union all select 3 as num from dual union all select 4 as num from dual union all select 5 as num from dual union all select 6 as num from dual union all select 7 as num from dual union all select 8 as num from dual union all select 9 as num from dual union all ... select 100 as num from dual;

but that's not only tedious but completely unnecessary. There are several much better ways to create row sources in SQL.

The simplest, and most frequently used, row generator uses CONNECT BY LEVEL:

select level from dual connect by level <= 100 ;

What's going on here?

- CONNECT BY is part of the hierarchical query clause
- LEVEL is a pseudocolumn available when using CONNECT BY.

And we can use this directly to solve FizzBuzz:

select case when mod(level,15)=0 then 'FizzBuzz' when mod(level,3)=0 then 'Fizz' when mod(level,5)=0 then 'Buzz' else to_char(level) end as "FizzBuzz!" from dual connect by level <= 100;

In 11.2 and above, Oracle supports Common Table Expressions, a.k.a. recursive subquery factoring. So we can rewrite the row generator using recursive subquery factoring, and then rewrite the fizzbuzz solution as:

WITH natural (n) as ( select 1 as n from dual union all select n+1 from natural where n<100 ) select case when mod(n,15)=0 then 'FizzBuzz' when mod(n,3)=0 then 'Fizz' when mod(n,5)=0 then 'Buzz' else to_char(n) end as "FizzBuzz!" from natural;

Unlike CONNECT BY, recursive subquery factoring is ANSI compliant. So the above gives us an ANSI-compliant solution to FizzBuzz.

Here's another, even simpler, way to get our 100 rows of numbers 1..100:

select * from xmltable ('1 to 100');

and also...

select rownum from ( select 1 from dual group by cube( 1, 1, 1, 1, 1, 1, 1 ) ) where rownum < 101;

Confused? Wonder what's going on here? Group by cube gives you every aggregation. In the case of "select 1 from dual", every aggregation is 1. Try this:

--2 rows select 1 from dual group by cube (1); --4 rows select 1 from dual group by cube (1,1); --8 rows select 1 from dual group by cube (1,1,1);

so the "group by cube" is creating 2^n rows for you, where "n" is the number of cols listed in the CUBE.

Now, we've looked at several ways to generate a table of N natural numbers from 1...N. Which is better / faster? In the next post we'll compare execution times, statistics and explain plans for the above row generators. Then, we'll look at a few more things you can do with row generators.

Comments ( 3 )

how about a CTE?

Seems like a CTE's performance would be the best.

Yes, you can see the CTE version about halfway thru the post. In Oracle, connect by level is slightly faster than CTE - see the next post in the series

for details:

https://blogs.oracle.com/sql/entry/row_generators_part_2

Solution without case when

select rownum,replace(replace(replace(replace(','||rownum||','

,','||trunc(rownum/15)*15||',','FizzBuzz')

,','||trunc(rownum/3)*3||',','Fizz')

,','||trunc(rownum/5)*5||',','Buzz')

,',','')

from dual connect by level<=100