Hi,
Thanks Geerlings, Gaurav, Martin and all of you.
I have tested Gaurav method, create table as select * from ...order
by...
It seems dose not work, I will try Geerlings' way later
Thank you all very much !
Jim
>-----Original Message-----
>From: Geerlings Mark [mailto:oracledba-ezmlmshield-
>x23282258.[Email address protected]
>Sent: Wednesday, September 27, 2006 5:28 PM
>To: LazyDBA Discussion
>Subject: RE: select distinct trouble (urgent)
>
>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.
>
>
>
>
>
>
>---------------------------------------------------------------------
>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