Re: Query returning results in different order

Hi Jeff,

It's getting late so please forgive me if I'm missing something really obvious but what do you mean by saying the data is not "ordered correctly". The only way you can *guarantee* the order of returned rows is to specify an order by clause (distinct although it does perform some sorting doesn't guarantee data will be returned in a specific order).

Therefore it's quite expected for your data to not necessarily be returned in an expected order.

Regards

Richard
"Jeff Turner" <zigjst_at_hotmail.com> wrote in message news:c73aa58a.0207190539.7b3ad12d_at_posting.google.com...
> 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 - 16:06:48 CEST