pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNION and ORDER BY ... IS NULL ASC
4+ messages / 4 participants
[nested] [flat]
* UNION and ORDER BY ... IS NULL ASC
@ 2003-04-05 01:58 A.M. <agentm@cmu.edu>
2003-04-05 02:49 ` Re: UNION and ORDER BY ... IS NULL ASC Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-05 02:52 ` Re: UNION and ORDER BY ... IS NULL ASC Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 4+ messages in thread
From: A.M. @ 2003-04-05 01:58 UTC (permalink / raw)
To: pgsql-sql
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3
I have come across some unexpected behavior while dealing with a UNION
and ORDER BY. I'd like some advice. Here's a scenario where I want to
order by null values:
CREATE TABLE test(a int);
SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC;
returns:
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
whereas:
SELECT a FROM test UNION SELECT a FROM test ORDER BY a;
works fine. The column name is the same in both queries, yet I get an
error! Obviously, this is a gross oversimplification of what I want to
do, but I couldn't get it working in this minimal case. I also tried
using the column number, and that returns the same results as the name.
What am I doing wrong? Thanks for any info.
><><><><><><><><><
AgentM
agentm@cmu.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UNION and ORDER BY ... IS NULL ASC
2003-04-05 01:58 UNION and ORDER BY ... IS NULL ASC A.M. <agentm@cmu.edu>
@ 2003-04-05 02:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-04-05 02:49 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; +Cc: pgsql-sql
On Fri, 4 Apr 2003, A.M. wrote:
> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3
>
> I have come across some unexpected behavior while dealing with a UNION
> and ORDER BY. I'd like some advice. Here's a scenario where I want to
> order by null values:
>
> CREATE TABLE test(a int);
> SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC;
>
> returns:
>
> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns
>
> whereas:
>
> SELECT a FROM test UNION SELECT a FROM test ORDER BY a;
>
> works fine. The column name is the same in both queries, yet I get an
> error! Obviously, this is a gross oversimplification of what I want to
> do, but I couldn't get it working in this minimal case. I also tried
> using the column number, and that returns the same results as the name.
> What am I doing wrong? Thanks for any info.
Try something like:
select a from (select a from test union select a from test) as foo order
by a is null asc;
It's an unsupported extension (I don't think SQL would allow
"order by a is null" in the first place) so you'll have to workaround for
now.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UNION and ORDER BY ... IS NULL ASC
2003-04-05 01:58 UNION and ORDER BY ... IS NULL ASC A.M. <agentm@cmu.edu>
@ 2003-04-05 02:52 ` Josh Berkus <josh@agliodbs.com>
2003-04-05 04:17 ` Re: UNION and ORDER BY ... IS NULL ASC Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2003-04-05 02:52 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; pgsql-sql
AM,
> CREATE TABLE test(a int);
> SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC;
>
> returns:
>
> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns
I do believe that this is per the SQL spec; UNIONs may only be sorted on the
output columns.
So if you did
SELECT a, (a IS NULL) as test_a
FROM test
UNION
SELECT a, (a IS NULL)
FROM test2
ORDER BY test_a
... it should work.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UNION and ORDER BY ... IS NULL ASC
2003-04-05 01:58 UNION and ORDER BY ... IS NULL ASC A.M. <agentm@cmu.edu>
2003-04-05 02:52 ` Re: UNION and ORDER BY ... IS NULL ASC Josh Berkus <josh@agliodbs.com>
@ 2003-04-05 04:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-04-05 04:17 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: A.M. <agentm@cmu.edu>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
>> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
>> the result columns
> I do believe that this is per the SQL spec; UNIONs may only be sorted on the
> output columns.
Right. We extend the SQL spec for ORDER BY on simple selects, but we
have not gotten around to doing so for ORDER BY on set-operation
results. You can only sort by unadorned output columns.
Patches to improve this are welcome ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-05 04:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-05 01:58 UNION and ORDER BY ... IS NULL ASC A.M. <agentm@cmu.edu>
2003-04-05 02:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-05 02:52 ` Josh Berkus <josh@agliodbs.com>
2003-04-05 04:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox