Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Fri Dec 10 2004 - 11:39:29 CST