Re: Need to overcome UNION / ORDER BY restriction - Mailing list pgsql-sql

"Timo" <siroco@suomi24.fi> writes:
> SELECT * from foo where priority = 1 order by seniority
>     union select * from foo where priority > 1 order by seniority, priority
> but this gives parse error because of the restrictions with ORDER BY and
> UNION (I suppose..)

You'd need to parenthesize:

(SELECT * from foo where priority = 1 order by seniority)
UNION ALL
(select * from foo where priority > 1 order by seniority, priority)

Otherwise the ORDER BY is considered to apply to the whole UNION result
(it's effectively got lower binding priority than the UNION).  Note also
that you *must* use UNION ALL, else UNION will attempt to eliminate
duplicates, and mess up the sort order while at it.

See also Bruno's solution nearby.  Not sure which of these approaches
would be faster; try both.
        regards, tom lane