Union query with single order by clause

[Venkat Sadasivam]

We want to write a union query combining 3 SELECT  and have a single order by clause. Is it possible do with jOOQ? If so can we get an example?

[Lukas Eder]

Hi Venkat,

jOOQ's (historic) understanding of UNIONs is documented here:

http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/select-statement/union-clause/#N10EC1

This diverges from the SQL standard, where ORDER BY is applied to a whole set of subqueries. A fix for this is scheduled for jOOQ 3.3:

https://github.com/jOOQ/jOOQ/issues/1658

In the mean time, you can work around this limitation by using a derived table:

ctx.select()

   .from(

      select(A)

     .from(T1)

     .union(

      select(B)

     .from(T2))

     .asTable()

    )

   .orderBy(...)

Note that you may have to specify an alias for your derived table, in order to access its columns.

Hope this helps,

Lukas

2013/12/6 Venkat Sadasivam <venka...@gmail.com>

We want to write a union query combining 3 SELECT  and have a single order by clause. Is it possible do with jOOQ? If so can we get an example?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[she...@sminq.com]

How do we achieve this with the latest 3.8 how do we use the derive table alias to access the sort column?

[Lukas Eder]

Hi Sheldon,

Thank you very much for your enquiry. The jOOQ API has been fixed a while ago to support SQL standard UNION .. ORDER BY statements. Just write:

DSL.using(configuration)

   .select(...)

   .union(select(...))

   .union(select(...))

   .orderBy(...)

I hope this helps,
Lukas

For more options, visit https://groups.google.com/d/optout.

[Sheldon D'Souza]

Thanks will check it out

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/pFeYDbjYXhc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.