99 Bottles of Beer

Voting

Please select* Bad as Hell** Nothing new*** Good**** Nice Coding***** TOP GEEK

Category

real language

Bookmarking

select 
    CASE (a.aa * 10 + b.bb)
        WHEN 0 THEN 'No more bottle of beer on the wall, no more bottles of beer. ' +
                   'Go to the store and buy some more, 99 bottles of beer on the wall.'
        WHEN 1 THEN '1 bottle of beer on the wall, 1 bottle of beer. ' +
                    'Take one down and pass it around, no more bottles of beer on the wall.'
        WHEN 2 THEN '2 bottles of beer on the wall, 2 bottles of beer. ' +
                    'Take one down and pass it around, 1 bottle of beer on the wall.'
        ELSE 
            cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer on the wall, ' +
            cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer. ' +
            'Take one down and pass it around, ' +
            cast((a.aa * 10 + b.bb)-1 as varchar(2)) + ' bottles of beer on the wall.'
    END
from 
    (select 0 as aa union select 1 union select 2 union select 3 union select 4
    union select 5 union select 6 union select 7 union select 8 union select 9) a
cross join 
    (select 0 as bb union select 1 union select 2 union select 3 union select 4
    union select 5 union select 6 union select 7 union select 8 union select 9) b
order by a.aa desc, b.bb desc

Alternative Versions

Version	Author	Date	Comments	Rate
Oracle 9i+ hierarchical query	Christoph Linder	11/21/07	0
DB2 / ANSI	Kent Olsen	11/29/05	1
MS-Transact-SQL 8.00	Karl	07/06/05	0
Self contained Oracle 9i+ Version	Chris Farmer	11/10/05	0
MS SQL 2005	Johan Åhlén	09/28/09	1
CROSS JOIN - UNPIVOT - SQL SERVER 2008	Nick Jacobsen	09/18/11	0
standard version	Rob van de Pol	04/20/05	0
Oracle, DBA Version	Thomas Rein	02/03/07	0
DB2, no redundancy, with recursion	Joachim Banzhaf	05/17/06	0
Produces the exact lyrics, ORACLE	Ernst Madsen	09/23/05	0

Comments

 Ernst Madsen said on 11/30/05 16:24:54

Nice idea, but it wont work as is. It misses a lot of "from dual" or something.
It can't possible have been tested, unless some SQL dialect assumes "from dual" when no table is mentioned.

 Jerry said on 01/11/06 19:23:07

Works like a charm... just tried it myself on a SQL2000 box. Microsoft (as usual) don't do everything according to the industry standards.....

 Luis Carlos F. Dias said on 01/14/06 23:10:11

Yes, it works...MS-SQL does not need the 'from dual' statement. It works fine even in PostgreSQL changing only the concatenation operator '+' by '||'.

 MaS said on 08/21/06 19:55:50

Doesn't print *TWO* lines for each bottle.

Add Comment

Please provide a value for the fields Name, Comment and Security Code.
This is a gravatar-friendly website.
E-mail addresses will never be shown.
Enter your e-mail address to use your gravatar.

Please don't post large portions of code here! Use the form to submit new examples or updates instead!

Name:

eMail:

URL:

Security Code:
  
Comment: