How to tell oracle to sort by a specific sort order passed in from java?

Question

Here's what I need to be able to do.

I have a List in java which I can convert to comma separate string of IDs like so "3,4,5,6,1,2"

I wonder if there's way to pass that string to oracle and have sql code sort based on that string's sort order?

So this query:

select t.id
from t_test t

Would result in this order

ID
3
4
5
6
1
2

Answer 1

If you can modify the query in java, you could do something like this:

SELECT t.id
FROM t_test t
ORDER BY DECODE(t.id, 3, 'A', 'B') ASC,
         DECODE(t.id, 4, 'A', 'B') ASC,
         DECODE(t.id, 5, 'A', 'B') ASC,
         DECODE(t.id, 6, 'A', 'B') ASC,
         DECODE(t.id, 1, 'A', 'B') ASC,
         DECODE(t.id, 2, 'A', 'B') ASC;

You have to put a decode in the order by clause for each element in the list. The second parameter in each decode is one element of the list.