All you can do is cuss at the developers ... without an ORDER BY, you just
can't count on
controlling that order. You could put some sort of kludge in place that
seems to work, and
years down the line someone else will cuss at you because their new version
of Oracle --
or their migration to another RDBMS -- returns rows not in the "correct"
order.

Bill


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





"Shi Jim -
Workstream "
<oracledba-ezmlms To
hield-x39884634.x "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
a.com> cc

09/27/2006 02:54 Subject
PM RE: select distinct trouble
(urgent)










Hi, Martin:

Thank you for your advice! I exactly agree with you !
But, this is not I can control, because application is developed several
years ago, we migrate the database recently and get the trouble.
And I have to figure out what I can do without rewrite thousands SQLs in
application.


Thanks

Jim

>-----Original Message-----
>From: Martin Goodson [mailto:oracledba-ezmlmshield-
>x40018877.[Email address protected]
>Sent: Wednesday, September 27, 2006 2:31 PM
>To: LazyDBA Discussion
>Subject: Re: select distinct trouble (urgent)
>
>Shi Jim - Workstream wrote:
>> Hi, DBAs:
>>
>>
>>
>> Does anybody know how to force "select distinct ...." and "group by
"
>> to sort the result set when not use "order by" in Oracle 10g, the
>> results set is sorted in Oracle8i , but not get same sorted result
set
>> in Oracle 10g using same SQL, We met this trouble when we migrate
>> database from 8i to 10g for a Production database. I tried NLS_LANG
and
>> NLS_SORT environmental parameter, it doesn't work, any initial
parameter
>> or environmental parameters we can use to force to sort for SELECT
>> distinct or group by?
>
>Personally I can only offer this single piece of advice:
>
>Never, ever, EVER, rely upon Oracle returning your data in the order
you
>want 'because it has in the past' or 'that's how it usually does it'.
>Data is not guaranteed to be returned in a specific order.
>
>If your data is required in a specific order then use an ORDER BY.
>
>--
>Martin
>
>'Things need not have happened to be true. Tales and dreams are the
>shadow-truths that will endure when mere facts are dust and ashes,
>and forgot' - Dream Country, Neil Gaiman
>
>blackberry: !lirameak![Email address protected]
>(For spam protection the word wrapped in !s is spelt backwards)
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





Oracle LazyDBA home page