Re: Query returning results in different order

Hello Jeff,

the order of a result set is ONLY guaranteed when the ORDER-BY clause has been used. Apart from select distinct, one can find that with a group-by, the order of results isn't ordered.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv

Jeff Turner wrote:

>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
Received on Fri Jul 19 2002 - 09:06:18 CDT