UNION and ORDER BY ... IS NULL ASC

From: "A(dot)M(dot)" <agentm(at)cmu(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: UNION and ORDER BY ... IS NULL ASC
Date: 2003-04-05 01:58:38
Message-ID: 1E4E6631-670A-11D7-89FA-0030657192DA@cmu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)cmu(dot)edu

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Manfred Koizar 2003-04-05 02:08:22 Re: [SQL] can i make this sql query more efficiant?
Previous Message Claude 2003-04-05 00:21:36 Re: timestamp with postgresql 7.3