sql - 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.