Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query returning results in different order

Re: Query returning results in different order

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 20 Jul 2002 00:06:48 +1000
Message-ID: <0XUZ8.38701$Hj3.117017@newsfeeds.bigpond.com>


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 - 09:06:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US