Re: "order by" with "where value in" clause problem

On 12 Jul 2005 15:08:00 -0700, "Sharkie" <sharkdba_at_yahoo.com> wrote:

>Hello,
>
>I'm using Oracle 8i on Solaris box. I have a list of values which
>will be passed to oracle "where in" clause.
>
>for example, if id list = 1, 5, 80, 13, 4
>
>This list will be passed to query where in clause:
>
>select id, col1, col2, col3
>from table1
>where id in (1, 5, 80, 13, 4)
>
>I need to make sure the select statement will return rows in the exact
>order
>as the id list. Without an "order by" clause oracle returns them in
>whatever
>order it likes, and I don't know what kind of order by clause would
>work here.
>
>How can I assure the sort in this case? The id list is dynamically
>generated,
>I will never know number of elements, and what order they will be.

can only be done when you add a dummy column decode(id, 1,1,5,2,80,3,13,4,4,5,NULL) pos and add
order by pos nulls last

But then you would still question what pressing business needs you are trying to resolve. Sets are by design unordered.

--
Sybrand Bakker, Senior Oracle DBA