Re: UNION?

Forest Felling <res08i7v(at)verizon(dot)net> writes:
> mydb=# select count(city) as qty, code as code
> from test_year
> where length(code) = 1
> group by code
> order by code
> union all
> select count(city) as qty, 'All' as code
> from test_year
> where length(code) = 1
> ERROR: parser: parse error at or near "all"

If you want to use ORDER BY in one of the elements of a UNION (which is
not legal per standard SQL), you need to parenthesize:

(select count(city) as qty, code as code
from test_year
where length(code) = 1
group by code
order by code)
union all
(select count(city) as qty, 'All' as code
from test_year
where length(code) = 1)

The reason for this can be seen by considering this variant query:

(select count(city) as qty, 'All' as code
from test_year
where length(code) = 1)
union all
(select count(city) as qty, code as code
from test_year
where length(code) = 1
group by code
order by code)

Without the parens, it would be unclear whether you mean the ORDER BY
to apply to the second sub-SELECT, or to the result of the whole UNION.
(SQL spec would require it to be interpreted as applying to the whole
UNION result, I believe.)

regards, tom lane