How to sort for the decoded value and not for the stored one?

[Alex B]

Hi, 

a little bit of context on this weird subject first :)

We face an issue with sorting our data because we are using Enum type to manage some of our columns. That said, on DB side ( Oracle FWIW ) we store an NUMBER value that is decode java side to show a String Label. Of course when we try to order by, we got int ordering, that does not match String Label ordering, but we want to order on DB because we also have pagination in place, so order must be done before paging of course. 

Is there a way to do the correct  string ordering "after decode" and get it translated in DB ordering at query time? Such as in memory java decode, or don't know temporay tables that exists only in java to join with regular queries on db.. I have no idea.. 

Thank you

[Lukas Eder]

Hi Alex,

You can use these utilities:

- https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Field.html#sortAsc(java.util.Collection)

- https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/Field.html#sort(java.util.Map)

This is just convenience for using a CASE expression in your ORDER BY clause. jOOQ calls this "sort indirection":

https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/order-by-clause/order-by-case/

You can do this dynamically, of course, i.e. using a utility that works for all of your enum types.

I 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+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/5676122e-e9af-4aa8-bb7c-e4f4b2b51393n%40googlegroups.com.

[Alex B]

Indeed, it worked like a charm! and it's also to easy to implement, shame on me to not check docs properly.

But really, do you have an answer for every SQL problem out there? :p Outstanding!

Thank you.

Alex

[Lukas Eder]

On Tue, Feb 24, 2026 at 4:27 PM Alex B <alessandr...@gmail.com> wrote:

But really, do you have an answer for every SQL problem out there?

Yes