Re: Query returning results in different order

In article <c73aa58a.0207190539.7b3ad12d_at_posting.google.com>, zigjst_at_hotmail.com says...
>
>I'm having a heck of a time figuring this out and I'm hoping someone
>can help.
>
>I've got a SQL statement that I can run on two different Oracle
>servers with different results. The data returned is the same, however
>the order of the result set is different.
>
>It returns correctly on an Oracle 8.1.6.0 server, but does not on
>8.1.6.3.0. The database schemas & data are identical on both servers.
>Here is the SQL statement:
>
>
>SELECT DISTINCT
>VTREATMENTS.TREATMENTID AS "Treatment ID",
>VAPPLICATIONS.APPLICATIONNUM AS "Application No",
>VSTUDYACTIVEINGREDIENTS.ISOFINTEREST AS "AI Of Interest"
>FROM
>VTREATMENTS, VAPPLICATIONS, VSTUDYACTIVEINGREDIENTS
>WHERE
>VSTUDYACTIVEINGREDIENTS.ISOFINTEREST= 'Yes' AND
>VTREATMENTS.TrialNo = VAPPLICATIONS.TrialNo AND
>VTREATMENTS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND
>VTREATMENTS.TreatmentNo = VAPPLICATIONS.TreatmentNo AND
>VAPPLICATIONS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND
>VTREATMENTS.TrialNo = 284 AND VSTUDYACTIVEINGREDIENTS.StudyNo = 121
>
>
>The statement is generated by an application and I have no control
>over it (iow I can't just tack on an ORDER BY to it), but I can
>control the order of the views it is selecting from. I also have no
>control over changing the version of the Oracle server.
>
>The funny thing about this is if I remove the first WHERE clause
>(ISOFINTEREST = 'Yes') it returns in the proper order on the 8.1.6.3
>server!
>
>any insight would be very appreciated.... thanks

Unless there is an ORDER BY, there is no correct order of the data. It is allowed (and supposed) to come out in any old order it wants to. In fact, the same query in the same database could return the data in a different order between two executions.

No bug here, nothing to fix, nothing that CAN be fixed. The *only* way to get sorted data -- repeat the only way -- is to have an order by. Period.

--
Thomas Kyte (tkyte_at_oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp