Hi Lukas, Thank you for the explanation!
So jOOQ has reversed functionality compared to standard SQL on this matter: jOOQ: subqueries if you want "order by" over the whole query; no subqueries for "order by" per query part SQL: no subqueries if you want "order by" over the whole query; subqueries for "order by" per query part Doesn't look like "extra" functionality to me, just different :) Sander On Sep 19, 5:55 pm, Lukas Eder <[email protected]> wrote: > 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
