Michael Wood <esiotrot@gmail.com> writes:
> On 22 July 2010 18:26, gargoyle60 <gargoyle60@example.invalid> wrote:
>> This works fine as above but as soon as I reintroduce the ORDER BY clause I get the syntax error...
> I haven't tried it myself, but I think you need to put the whole
> "SELECT ... UNION ALL SELECT ..." in parentheses and then have the
> "ORDER BY ..." after that.
ORDER BY effectively binds less tightly than UNION, so adding
parentheses that way just specifies what the parser will assume by
default. You can force it to associate the ORDER BY with only the
second UNION arm by doing
(SELECT ...) UNION (SELECT ... ORDER BY ...)
but in most cases this is quite pointless, since the UNION will feel free
to not preserve the row ordering of its inputs. The default behavior
applies the ORDER BY after the UNION, which is usually what's wanted.
> Also, try 'ORDER BY "databaseName", ...'
> since you've basically renamed the columns.
Yes, that's the real problem: the output columns of the UNION are named
as specified by the AS clauses, so you need to use those names in an
ORDER BY that's meant to sort the UNION's output.
regards, tom lane