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 SQL

(standard version)

Date:04/20/05
Author:Rob van de Pol
URL:n/a
Comments:0
Info:n/a
Score: (2.75 in 8 votes)
remark	99 bottles of beer with Oracle SQL*Plus
remark	R.vandePol@voeding.tno.nl	< Rob van de Pol>
remark
remark	assuming that YourTable contains at least 99 rows ;-)
remark
remark	RowNum is an Oracle psuedo column indicating the sequence the rows
remark	were selected.

SELECT 	TO_CHAR(100-rownum)||' bottles of beer on the wall, ' ||
		TO_CHAR(100-rownum)||' bottles of beer,' ,
	'take one down and pass it around, ' ,
	DECODE ( TO_CHAR(99-rownum) , '0' , 'No more' , TO_CHAR(99-rownum) )||
		' bottles of beer,'
FROM 	YourTable
WHERE 	rownum < 100

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
DB2 / ANSIKent Olsen11/29/051
MS SQL 2000David Teviotdale09/07/054
MS-Transact-SQL 8.00Karl07/06/050
Self contained Oracle 9i+ VersionChris Farmer11/10/050
MS SQL 2005Johan ┼hlÚn09/28/091
CROSS JOIN - UNPIVOT - SQL SERVER 2008Nick Jacobsen09/18/110
Oracle, DBA VersionThomas Rein02/03/070
DB2, no redundancy, with recursionJoachim Banzhaf05/17/060
Produces the exact lyrics, ORACLEErnst Madsen09/23/050

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: