RE: Sorting in a query

Hmm.....no Oracle version....sigh....

Ok, I'll assume you're on at least 9i.

You can:

1.) Create a pipelined function that takes the input list of numbers, and returns tow columns, a row number and the data itself.

2.) Since it's a pipelined function, you can treat it as a table and join to it.

3.) Write your query with the pipelined function, join to the real table(s) and order by the data in the=20 pipelined function.

So, create a pipelined function that will work like this:

select number_of_row, number_data from
my_pipelined_func(:array_of_numbers);
1 1
2 9
3 3
4 7
5 10

Now, the final query would be:
select a.number_of_row, e.emp_id, e.ename=20   from emp e,=20

       my_pipeliked_func(:array_of_numbers) a  where a.number_data =3D e.emp_id
order vt a.number_of_row;

And it should return:

1 1 Allen
2 9 King
3 3 Brown
4 7 James
5 10 Martin

So, the pipelined function numbers the elements in the array, in the order
they appear. Then, when you join to the table, you simply order by that artifical column.

Hope that helps,

-Mark

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Gaston, William

> I have an odd problem. I have a list of numbers that serve as=20
> extract parameters in a query select ...where x in number list). =20
> The numbers are in a specific sequence which does not match=20
> the sequence of the extracted data. The user wants the data in=20
> the same order as the list of numbers supplied. Is there any easy=20
> way to do this within the query?
> =20
> Thanks for any help.
> =20
> Bill

>=20

--
http://www.freelists.org/webpage/oracle-l