select order by my_outside_order

Hello,

I have a vector of id's, and I need to get a query that returns a result set order by the vector order.

For example if I have a vector contains values:  [99, 98, 97, 96, 95, 94, 93, 92, 91, 90] I want the query to return the records in the order of appearance at the vector, something like this query:
  select id, col_1, col_2
    from t
    where id in (99, 98, 97, 96, 95, 94, 93, 92, 91, 90)     order by decode( id
, 99, 1
, 98, 2
, 97, 3
, 96, 4
, 95, 5
, 94, 6
, 93, 7
, 92, 8
, 91, 9
, 90, 10);

Note: The vector size is 10-20 id’s. What is the best way to perform this kind of queries?

I face this problem at my WEB application- 1. First cache the id's of a query the user sends (user choose where close and order by field)
select ID from myView where <where clause> order by <order field name>

2. User can now page results - the application server take a sub range from the cache and
select <fields> from myView where ID in ( :id1, :id2, .. :id10) order by <order field name>

If all records contain single value at <order field name> then the second select (the paging stage) will return records at the order they come from the view, which can be different from the order save at the cache because of different optimization plan. Because user can either 'page' results AND go one by one, user can get the results in different order each time.

TIA. Received on Wed Aug 07 2002 - 07:30:10 CDT