Hi, I am running a simple select query to get some numbers back like the following.

select * from TABLE where NUMBER in ('0334','0980','0116','0633','0116D','0115','0114' ,'0141','0635','8371','0240','0142','0316','2478', '0913','0108','0982','0731','0147','2460').

I am expecting the results in the order the IDs are mentioned in the query. But DB2 is by default sorting the number in the resultset. So the first record is showing as 0108 instead of 0334. Is there any way I can turn-off this default ordering of numbers.

Thanks in advance for your help.

The only way that I know to change the order of a result set is to use the ORDER BY clause. You'll need to get a bit tricky to get the apparently arbitrary order you want, but you can do it using a CASE statement in the ORDER BY clause.

-PatP

I think CASE can be used to substitute something in the results based on some condition. But in my case, I just need to display the numbers in the order it was given is the Query. I am still trying to find some solution.
Thanks again!

Try something like this...

select .... ,
case NUMBER
WHEN '0334' THEN 1
WHEN '0980' THEN 2 ....
...WHEN '2460' THEN 20
end as NUM_ORDER
from TABLE
where NUMBER in ('0334','0980','0116','0633','0116D','0115','0114' ,'0141','0635','8371','0240','0142','0316','2478', '0913','0108','0982','0731','0147','2460')
order by NUM_ORDER