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.

Views: 560

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 ;) 


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



Blog Posts

Looking to Publish Scientific, Technical and Medical Articles? Take a look!

Digital publishers who are involved in publishing Scientific, Technical and Medical (STM) content follow a very rigorous routine of editing, formatting, and publishing. Primarily, the content is meticulously scanned for grammatical errors and logical inequalities. Then a final draft is made in adherence to the guidelines set by the publishing platform and forwarded to TO BE PUBLISHED’ folder. All articles, papers, and…


Posted by Alvaro Dee on October 21, 2016 at 3:29am

Apple Opens Its First iOS App Development Centre in Italy

Apple aims at making things rounder and sleeker. With iPhone 7 out in the field, Apple is now investing its time and resources in organizing workshops and opening new iOS app development centers to educate people in advanced methods of iOS app building.

Apple’s highlight of the year? It’s first-ever iOS development Centre in Naples!…


Posted by Alvaro Dee on October 20, 2016 at 12:33am

Outsource Your Data Conversion Services to Get an Edge over Your Competitors

The process of converting data from one standard format to any desired file format, Data conversion makes the data more precise, convenient, and retrievable, while keeping the core data unchanged. The precious asset to an organization, data helps in effective decision making if digitized, organized, and managed properly. Whereas the inefficient management of data makes it a burden on the business enterprise.

If the data is structured and digitized efficiently, it can help in achieving…


Posted by Alvaro Dee on October 19, 2016 at 5:54am

Data Mining Can Help Your Business Get a Competitive Edge

Data mining services are a vital part of business growth. Data mining, sometimes referred as knowledge discovery in databases, is the process of digging through data to discover hidden connections, patterns, and anomalies so that future trends can be predicted. The mined data can help businesses in cutting costs, increasing revenues, reducing risks, improving customer relationships, and more.

The diversity and volume of data that is being captured by companies today is staggering. The…


Posted by Alvaro Dee on September 29, 2016 at 2:17am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service