Re: In Clause selecting in reverse order, wonder why?

Sybrand Bakker wrote:
> On 7 Mar 2005 10:09:36 -0800, deepesh.g_at_gmail.com (Deepesh Garg)
> wrote:
>
> >Hi,
> >I am running a query using an "in clause" in the condition. When no
> >order clause is given, the query selects the rows in the reverse
order
> >in which the ids appears in the in clause.
> >for example when I say select * from a_table where a_id in (1, 3, 9,
> >4);
> >the rows are selected in the order where a_id is 4, 9, 3, 1.
> >Tried with shuffling the ids and it always selects in the reverse
> >order of whatever is provided.
> >My query is:
> >1) Is it a standard behaviour? (of oracle, of sql)
> >2) Can I base my code upon this behaviour?
> >Thanks,
> >-Deepesh
>
> 1) You implicitly state you are still using the Rule Based Optimizer,
> which was obsoleted in 1994. You shouldn't use the RBO.
> RBO was parsing all statements from right to left/
>
> 2) Of course not. A set is unordered by definition. If you want your
> data ordered you should use ORDER BY. If you are going to rely on
this
> behaviour, one day your code will be broken.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Thanks for all your inputs. Time to write more code and handle the ordering :-).
What I didn't mention in the original post is that the ids are pre sorted on some criteria. A subset of these ids are used to select and fetch the complete row as required. So no ordering can be used in this query. Anyways since I have the original ids already in required order, will need to arrange the selected records based on that. Thanks again. Saved me to put a time bomb in code :-). -Deepesh Received on Mon Mar 07 2005 - 17:08:26 CST