Re: displaying result sets in the order listed in the IN list

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Sat, 17 Apr 2004 11:58:19 -0600
Message-id: <4081703B.FA628586@sun.com>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Apr 17 2004 - 12:59:01 CDT