Re: UNION?

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