Forest Felling <res08i7v@verizon.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