Re: displaying result sets in the order listed in the IN list
- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 17 Apr 2004 11:58:19 -0600
Jonathan,
Excellent point. I am both naughty and cute ;) (though not nearly as naughty as
Mogens nor as cute as Rachel)
I think the solution to the repeating values is very simple. Instead of
searching on the inlist value, search on the exact string (including the ,). To
account for the last value, append a ',' to the strings.
1 select n1,v1 from t1
2 where n1 in (790,7902,79)
3* order by instr('790,7902,79'||',',n1||',')
SQL> /
N1 V1
---------- ----------
790 ersfda
7902 bnasdff
79 asdf
Now I am off to ponder other cases where it won't work, like a case of Scotch...
Daniel
Jonathan Lewis wrote:
> Very cute, but I was about to write a note about
> naughty people and bind variables. Then I realised
> that there would be cases where it wouldn't work.
>
> Oracle 9.2
>
> SQL> select n1,v1 from t1
> 2 where n1 in (790,7902,79)
> 3 order by instr('790,7902,79',n1);
>
> N1 V1
> ---------- ----------
> 79 asdf
> 790 ersfda
> 7902 bnasdff
>
> 3 rows selected.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
