Hi Sander, Postgres (and most other RDBMS) are correct in not allowing ORDER BY clauses in a UNION's subselect. In relational thinking, this doesn't make much sense anyway, because a UNION is a set operation, which cannot guarantee any specific order anyway.
The reason why I designed things like this is because 1) I wasn't sure either, at the beginning, and 2) you may want to define an order along with a limit, e.g. to query for the top 5 bananas and union them with the top 5 oranges (I can't think of a real life example right now). This would have to be written like this: SELECT * FROM ( SELECT * FROM banana ORDER BY id LIMIT 5 ) UNION ( SELECT * FROM orange ORDER BY id LIMIT 5 ) The common case, however, is to order only the entire UNION, not each subselect. This is a "feature" of the jOOQ API. I'm currently not sure whether this should be corrected, though, as a correction will need some significant changes in the API... And maybe, the extra "functionality" may come in handy to some users... This is probably worth mentioning in the documentation: http://sourceforge.net/apps/trac/jooq/wiki/Manual/DSL/UNION Cheers Lukas 2011/9/19 Sander Plas <[email protected]>: > Hi all, > > Postgres seems to demand that you specify "ORDER BY" once per > "unioned" query. > > This works: > > => SELECT id FROM output UNION SELECT id FROM output ORDER BY id LIMIT > 1; > id > ------ > 2020 > (1 row) > > > This doesn't: > > => SELECT id FROM output ORDER BY id UNION SELECT id FROM output ORDER > BY id LIMIT 1; > ERROR: syntax error at or near "UNION" > LINE 1: SELECT id FROM output ORDER BY id UNION SELECT id FROM > outpu... > ^ > > ... jOOQ on the other hand, seems to assume that order by is specified > per "query part": > > This gives an "cannot find symbol" error on "orderBy": > > Static.jooq().selectFrom(Output.OUTPUT).union(Static.jooq().selectFrom(Output.OUTPUT)).orderBy(Output.ID); > > This works (well, at least my IDE allows me to type this): > > Static.jooq().selectFrom(Output.OUTPUT).orderBy(Output.ID).union(Static.jooq().selectFrom(Output.OUTPUT).orderBy(Output.ID)); > > I think Postgres is right here, but i'm not 100% sure. > > - Sander
