Emulate DISTINCT ON in Oracle

[Mark L.]

Hi,

I tried:

        Result<Record2<BigInteger, BigInteger>> record = create
            .select(
                TEST.ID, TEST.SESSION_ID
                ).distinctOn(TEST.SESSION_ID).from(TEST)
            .orderBy(TEST.SESSION_ID, TEST.ID)
            .fetch();

which works perfectly on POSTGRES. But since Oracle isn't supporting that feature there will be an error.

Are there any plans for adding an emulation as stated in 2012:
https://stackoverflow.com/questions/10515391/oracle-equivalent-of-postgres-distinct-on/10515397#10515397

Greets, Mark

[Lukas Eder]

Hi Mark,

Thank you very much for your message. Indeed, DISTINCT ON is quite a handy PostgreSQL extension to the SQL standard. It's definitely possible to emulate it in trivial cases, but there are also a few non-trivial ones when you mix unions / order by / window functions, which must still produce the correct results.

There's a pending feature request on the roadmap:

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

But given the non-triviality of this emulation (both in general, as well as in jOOQ's internals), I cannot make any promises yet as to when this can be supported.

In order to get vendor-agnostic functionality here, I suspect you'll need to emulate the clause manually.

Hope this helps,
Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.