99 Bottles of Beer

Voting

Bookmarking

Language SQLite3

(SQLite3 one select, without any tables)

Date:	10/24/10
Author:	Mateusz Adamowski
URL:	n/a
Comments:	0
Info:	http://www.sqlite.org/
Score:	(2.88 in 8 votes)

-- 
-- sqlite3 < beer.sql
-- 
SELECT
	CASE WHEN n.p = 0 THEN
		CASE WHEN n.n > 0 THEN n.n ELSE 'No more' END || ' ' ||
		CASE WHEN n.n = 1 THEN 'bottle' ELSE 'bottles' END ||
		' of beer on the wall, ' ||
		CASE WHEN n.n > 0 THEN n.n ELSE 'no more' END || ' ' ||
		CASE WHEN n.n = 1 THEN 'bottle' ELSE 'bottles' END ||
		' of beer. '
	ELSE 
		CASE WHEN n.n = 0 THEN 'Go to the store and buy some more,'
		ELSE 'Take one down and pass it around,' END || ' ' ||

		CASE WHEN n.n = 0 THEN 99 WHEN n.n = 1 THEN 'no more' ELSE n.n-1 END || ' ' ||
		CASE WHEN n.n-1 = 1 THEN 'bottle' ELSE 'bottles' END ||
		' of beer on the wall.'
	END
FROM
(
	SELECT 10 * t.x + o.x AS n, pa.p AS p
	FROM (
		SELECT 0 AS x 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 ORDER BY x
	) AS t
	CROSS JOIN (
		SELECT 0 AS x 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 ORDER BY x
	) AS o
	CROSS JOIN (
		SELECT 0 AS p UNION SELECT 1
	) AS pa
) AS n
ORDER BY n.n DESC, n.p;

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!

one program in 1500 variations