Re: UNION and ORDER BY ... IS NULL ASC

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