Voting

Category

real language

Bookmarking

Del.icio.us Digg Diigo DZone Earthlink Google Kick.ie
Windows Live LookLater Ma.gnolia Reddit Rojo StumbleUpon Technorati

Language MySQL

Date:08/29/06
Author:Richard Selby
URL:n/a
Comments:0
Info:n/a
Score: (2.76 in 21 votes)
--
-- 99 Bottles of Beer
-- MySQL version
-- Works in mysql query browser for MySQL 4.1 and above
---
-- Based on Chris Farmer's Oracle version
-- 
SELECT
    CASE (a.tens * 10 + b.units)
        WHEN 0 THEN
              CONCAT('No more bottles 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.')
        ELSE
               CONCAT((a.tens * 10 + b.units) , 
                  ' bottle', IF((a.tens * 10 + b.units)!=1, 's', ''),
                  ' of beer on the wall, ' ,
                  (a.tens * 10 + b.units),   
                  ' bottle', IF((a.tens * 10 + b.units)!=1, 's', ''),' of beer. ' ,
                  'Take one down and pass it around, ' ,
                  ((a.tens * 10 + b.units)-1) , 
		' bottle', IF(((a.tens * 10 + b.units)-1)!=1, 's', ''), 
                  ' of beer on the wall.')
        END as verse
 FROM 
    (select 0 as tens
	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  ) as a
CROSS JOIN
    (select 0 as units
	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  ) as b
ORDER BY a.tens DESC, b.units DESC;

Download Source | Write Comment

Alternative Versions

Comments

Download Source | Write Comment

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: