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: 576

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

Why Moving Data to XML is a Wise Decision?

What is XML?

Extensible Markup Language (XML) is a set of rules for creating documents in a format that can be read by humans as well as humans. XML's primary objective is to facilitate structured data sharing across diverse information systems, especially over the Internet. Also, it is utilized to encode documents and serialize data. Being an open standard, XML is recommended by the W3 Consortium.

Let’s understand what XML data…

Continue

Posted by Alvaro Dee on December 7, 2016 at 1:48am

Focus More on Intelligent Tasks by Outsourcing Repetitive Ones

One of the most valuable assets of an organization, data if managed properly helps companies, whether small or large scale, make informed business decisions. Having your data effectively managed by an expert with keen eye for numbers, and strong attention to details can help your business reach newer heights. However, the…

Continue

Posted by Alvaro Dee on December 6, 2016 at 8:32pm

Add Value and Improve the Quality of Your Data with Data Enrichment Services

Data from various sources such as customers, suppliers, communities arrives in various forms every day. Business organizations use this information to make informed decisions. Therefore, it is very important to make sure that this data is accurate. One way of ensuring it is through…

Continue

Posted by Alvaro Dee on December 5, 2016 at 9:19pm

College Paper Writing Service

Actually time is a limited resource, especially for college and academic students. Who have to deal with Time is a limited resource, especially for college students who have to compact with their extracurricular, studies, and assignment paper.

Although most of the students are very dedicated for their college life and college studies. But time is a valuable thing for every person especially for students. So if you have ever faced the pressure of working on multiple assignment…

Continue

Posted by david smith on December 4, 2016 at 10:23pm

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service