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

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

What to Expect When Investing in Data Cleansing Services?

Why invest in data cleansing services?

Imagine a scenario- You are maintaining huge databases containing information about your potential customers and you are all geared up with next direct mailing campaigns. After promotional mailers have been sent, you realize the fact that the major part of your database actually contained supplier information.

Such blunders can happen anytime and can…

Continue

Posted by Alvaro Dee on July 25, 2016 at 11:30pm

Avail Affordable and Specialized Android Games Development Services

Cellular gaming has become hugely popular in the last few years. With cellular gaming and amazing features of the Android operating system mixed together, the demand for Android games development services has increased drastically. Gaming becomes fun on Android based Smartphones with its great features getting the users hooked from the word go!

Android supports clarity…

Continue

Posted by Alvaro Dee on July 24, 2016 at 10:47pm

All You Need To Know About OpenCart

What is OpenCart?



OpenCart is one of the largest open source eCommerce community, which is turn-key ready "out of the box" shopping cart solution. It is as simple as installing, selecting a template, adding products and being sales ready. It comes loaded with order management and multiple gateway features built-in, including lifetime free software and software updates. Some of the key features of OpenCart are unlimited categories, unlimited products, unlimited…

Continue

Posted by Alvaro Dee on July 5, 2016 at 2:32am

How Amazon Store Management Services can help Sellers to Effectively Manage their Store

Amazon provides an easy to navigate, user friendly and competitive platform for sellers. However, it can still get difficult for sellers to manage their Amazon store on their own.

If you have products to sell and you are not selling on Amazon because you lack the expertise or the resources to take your business one step further, Data4Amazon offers …

Continue

Posted by Alvaro Dee on May 29, 2016 at 10:58pm

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service