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

On 7 Mar 2005 10:09:36 -0800, deepesh.g_at_gmail.com (Deepesh Garg) wrote:

>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)

 Results without an order by can be returned in any order.

>2) Can I base my code upon this behaviour?

 Absolutely not. The reverse order compared with your "IN" list is a coincidence, based perhaps on physical storage order, the optimiser plan chosen, and/or non-deterministic factors. If you want a reliable order you must specify an order by.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool