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
>


Oracle LazyDBA home page