Keep order from 'IN' clause

Question

Is it possible to keep order from a 'IN' conditional clause?

I found this question on SO but in his example the OP have already a sorted 'IN' clause.

My case is different, 'IN' clause is in random order Something like this :

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)

I would like to retrieve results in (45,2,445,12,789) order. I'm using an Oracle database. Maybe there is an attribute in SQL I can use with the conditional clause to specify to keep order of the clause.

Answer 1

There will be no reliable ordering unless you use an ORDER BY clause ..

SELECT SomeField,OtherField FROM TestResult  WHERE TestResult.SomeField IN (45,2,445,12,789) order by case TestResult.SomeField          when 45 then 1          when 2  then 2          when 445 then 3          ...          end 

You could split the query into 5 queries union all'd together though ...

SELECT SomeField,OtherField FROM TestResult  WHERE TestResult.SomeField = 4 union all SELECT SomeField,OtherField FROM TestResult  WHERE TestResult.SomeField = 2 union all ... 

I'd trust the former method more, and it would probably perform much better.

Answer 2

Decode function comes handy in this case instead of case expressions:

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)
ORDER BY DECODE(SomeField, 45,1, 2,2, 445,3, 12,4, 789,5)

Note that value,position pairs (e.g. 445,3) are kept together for readability reasons.