Re: default select order

"Maija-Leena" <kangasmaki_at_netti.fi> wrote in message news:xkj9l.39$0s3.4_at_read4.inet.fi...
> Hi,
>
> database version is 10.2.0.1.0.
>
> I'm going to fix this of course by adding the missing order by -clauses to
> the program, the problem is that I cannot deliver it very easily to the
> customer and that's why I was hoping to find some kind of work around that
> could be done in the database and would work even a little better for a
> week
> or two. I find this very interesting behaviour because it's different what
> I've used to in Oracle7.
>
> Thanks for all the answers,
>
> Maija-Leena
>
>
>
>
> "Maija-Leena" <kangasmaki_at_netti.fi> wrote in message
> news:NU19l.116$Vl4.60_at_read4.inet.fi...
>> Hi,
>> we have changed from Oracle 7 to Oracle10g2 and found a mistake from our
>> program that didn't appear in the old environment.
>>
>> That is, we have two select-statements that should return rows in the
>> same
>> order but there is no order by -clause.
>> Now I'm wondering why 10g2 works like this (every time I query)? What
>> determines the order of the returned rows?
>> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
>>
>> A
>>
>> V
>>
>> E
>>
>>
>> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
>>
>> Avoin (refers to A)
>>
>> Ei kaytossa (refers to E)
>>
>> Valmis (refers to V)
>>
>> If I change the first query to SELECT X,Y, then I get the same order than
>> the last query. Column Y is not in primary key or any index. Is there a
> way
>> to fix this in the database ?
>>
>> Regards,
>>
>> Maija-Leena
>>
>>
>
>

NO, you have been relying on a behavior that you observed. You got lucky. If you do not specify an order by clause then the order the results come back in are unspecified. You are not guarrenteed that the order will be the same ever. This is STANDARD intor to how RDBMS's work. It is basic Codd. I have seen so many "programmers" actually argue over this basic stuff, but the db is doing this and so .... Think of the select statement as a type of function call. The actual statement is the parameter to the call. One of the bits you can specify in the function call is the order of the data. This bit is optional. If you don't specify it then the function is free to determine how to get and order the data. If you specify the order then the function HAS to give you the data in that order. Got it? Jim Received on Thu Jan 08 2009 - 05:19:46 CST