I agree with the good comments from Mr. Goodson and Mr. Koster, Oracle
queries always need an "order by" clause to guarantee a particular
order.
But there is another option that may help besides rewriting all of the
code. That is to use views. You would have to create a view for every
table that has the problem, and this is a multi-step process, and this
assumes that you always want the results from a particular table in the
same order. Here is an example of the steps for a table named "table1":
1. get a list of the grants on table1
2. rename table1 to table1_base
3. create view table1 as select * from table1_base order by [whatever
order you want];
4. create grants for the new table1 (view) matching the grants for the
original table1
I have used views somewhat like this in the past with "canned"
application to solve similar problems. Most applications will just
happily use the views and not notice that someone renamed the tables.
-----Original Message-----
From: Shi Jim - Workstream
[mailto:oracledba-ezmlmshield-x39884634.[Email address protected]
Sent: Wednesday, September 27, 2006 2:55 PM
To: LazyDBA Discussion
Subject: 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
THE INFORMATION CONTAINED IN THIS E-MAIL MESSAGE AND ANY ATTACHMENTS SENT FROM GENTEX CORPORATION IS GENTEX CONFIDENTIAL INFORMATION INTENDED ONLY FOR THE PERSONAL USE OF THE INDIVIDUAL OR ENTITY NAMED ABOVE. If you are not the intended recipient, you are hereby notified that any review, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete this e-mail message and any attachments from your computer.
Oracle LazyDBA home page