Hi , 

in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?

select distinct  Report_137__1_ . COUNTRY_NM   COUNTRY_NM  from (select distinct  v . CTMS_TRIAL_ALIAS_CODE ,  v . CLINICAL_TRIAL_ID ,  c . GEO_BOUNDARY_ID ,  c . COUNTRY_NM ,  c . CANCEL_INDC ,  c . STOP_INDC  from  CTMS_CLINICAL_TRIAL_ID_V   v ,  TRIAL_COUNTRY_V   c  where  v . CLINICAL_TRIAL_ID = c . CLINICAL_TRIAL_ID )  Report_137__1_

 

above is a  simple select statement. Thanks much.

Tags: PL/Sql, by, clause, order

Views: 319

Reply to This

Replies to This Discussion

The problem is simple: The query does not have an ORDER BY clause.

If you want Oracle to guarantee a specific order of a result set *every time you run a query* you have to use an ORDER BY.

But does this means in 10g we do not need to mention order by clause and still we get the result in alphabetical order i.e. ascending order.i understand that we need to mention order by clause to get the result in asc/desc order.

but why here in 10g i am getting the result in asc order without mentioning the order by clause and in 11g m not.

does 10g ,11g have diff in sorting the result set?

 

This means that the query's optimizer plan in 11g is not the same as 10g.

I suggest you read this blog post by Tom Kyte: http://tkyte.blogspot.com/2005/08/order-in-court.html

Thanks a lot , this does help. hope i would be able to make business user understand this ;) 

RSS

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Advantages Of The Oracle E-Business Suit

The Oracle EBS is a cost effective solution to all the ERP needs of your business. The following article talks about the advantages that this Oracle product has to offer. Read on to know.…

Continue

Posted by Michael Warne on April 22, 2015 at 2:00am

SAP InfiniteInsight Training

Tekslate.com is the Industry leader in providing  SAP InfiniteInsight Training across the globe. Our online training methodology focus on hands on experience of SAP InfiniteInsight

The objective of this training is to provide hands on experience and in depth understanding of the following modules of  SAP InfiniteInsight Training

 

 

Course…

Continue

Posted by Mohd Azher on April 9, 2015 at 4:19am

JIRA Training

Tekslate.com is the Industry leader in providing  JIRA Training across the globe. Our online training methodology focus on hands on experience of JIRA

The objective of this training is to provide hands on experience and in depth understanding of the following modules of  SAP InfiniteInsight Training

 

Course modules:

  • ·          Introduction to…
Continue

Posted by Mohd Azher on April 9, 2015 at 4:15am

DataStage Training

Tekslate.com is the Industry leader in providing as DataStage Training  across the globe. Our online training methodology focus on hands on experience of Datastage

The objective of this training is to provide hands on experience and in depth understanding of the following modules of  DataStage

Course…

Continue

Posted by Mohd Azher on April 3, 2015 at 6:45am

© 2015   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service