Re: ORDER BY col is NULL in UNION causes error?

On Dec 26, 2006, at 18:39 , Mike Benoit wrote:

> Fails
> ---------------------
> select * from income_tax_rate_us UNION select * from
> income_tax_rate_us
> order by state is null;
> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns

Even though state is a column in both tables, the order by is using
an expression, rather than a column.

Should work:

SELECT *, state IS NULL AS state_is_null
FROM income_tax_rate_us
UNION
SELECT *, state IS NULL AS state_is_null
FROM income_tax_rate_us
ORDER BY state_is_null

This should also work:

SELECT *
FROM (
SELECT *
FROM income_tax_rate_us
UNION
SELECT *
FROM income_tax_rate_us
) union_result
ORDER BY state IS NULL

I'm not sure of the underlying reasons why your query doesn't work,
but give these a shot.

Michael Glaesemann
grzm seespotcode net