Re: How to get "...WHERE test IN (5,23,2,12,56)" in this order

> Hello!
>
> SELECT ...WHERE product_id IN (5,23,2,12,56)
>
> I try to get a list of records in the same order like inside the
IN-clause.
> I do not have a field to sort these and dependent from user- Input its
> always different (2 to 6 items).
>
> Is this possible? or is there a different way to get records in a
particular
> order without having a field which I can use to sort?

You could create function like this

create function sort(product_id number,sort1 number default null, sort3 number default null,...,sort6 number default null) return number is

begin

if product_id=sort1 then
return 1;
end if;

if product_id=sort2 then
return 2;
end if;

...

end;

so you use:

SELECT ...WHERE product_id IN (5,23,2,12,56) order by sort(product_id,5,23,2,12,56);

This is not so good approach, because you limit number of arguments. Better way is to declare second parameter as table, so you can have any number of parameters, but that's little more complicated. Received on Thu Jan 10 2002 - 05:08:43 CST