Thread: UNION and ORDER BY ... IS NULL ASC
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
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.
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 BerkusAglio Database SolutionsSan Francisco
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