X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BEF01475C3D
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 21:52:31 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 24076475B47
	for <pgsql-sql@postgresql.org>; Fri,  4 Apr 2003 21:52:31 -0500 (EST)
Received: from [216.135.165.74] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2945196; Fri, 04 Apr 2003 18:52:27 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "A.M." <agentm@cmu.edu>, pgsql-sql@postgresql.org
Subject: Re: UNION and ORDER BY ... IS NULL ASC
Date: Fri, 4 Apr 2003 18:52:30 -0800
User-Agent: KMail/1.4.3
References: <1E4E6631-670A-11D7-89FA-0030657192DA@cmu.edu>
In-Reply-To: <1E4E6631-670A-11D7-89FA-0030657192DA@cmu.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304041852.30121.josh@agliodbs.com>
X-Spam-Status: No, hits=-31.0 required=5.0
	tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,
	REPLY_WITH_QUOTES,UPPERCASE_25_50,USER_AGENT_KMAIL
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/77
X-Sequence-Number: 12778

AM,

> CREATE TABLE test(a int);
> SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC;
>=20
> returns:
>=20
> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of=20
> the result columns

I do believe that this is per the SQL spec; UNIONs may only be sorted on th=
e=20
output columns.

So if you did=20
SELECT a, (a IS NULL) as test_a
FROM test
UNION=20
SELECT a, (a IS NULL)
FROM test2
ORDER BY test_a

... it should work.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco