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

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 Consider Microsoft Exchange Server 2013 For Your Organization?

The Microsoft Exchange Server 2013 is packed with features that make Server management efficient, effective and easy. The following article takes a look at the features of this Microsoft product and how it can help organizations manage their servers in a better way. Read on to know.



Server management can be a challenge for organizations and when taken lightly can cost organizations a fortune. Managing huge databases, confidential and sensitive information…

Continue

Posted by Michael Warne on March 28, 2015 at 2:08am

IBM WODM Online Training @VirtualNuggets:

Web URL       :  http://www.virtualnuggets.com/ibm-websphere-odm.html

Email               : info@virtualnuggets com

Contact           :+1-707 666 8949(USA)                     

                        : +91-888 556 0202(India) 

 

IBM WODM Online Training @VirtualNuggets:

 

           …

Continue

Posted by Thirupathi Myadaveni on March 16, 2015 at 3:30am

Oracle Reports as PL/SQL Stored Procedures

Would anyone be interested in a solution & framework that can create Reports generated as Text or HTML output using PL/SQL as the only programming language?



This would be useful for those developers that have skills in PL/SQL and would like to leverage the use of basic & advanced SQL & PL/SQL to create Database Stored Reports and call them within any preferred front-end or back-end technology.



In case those who are familiar with RPT/RPF (Oracle's first…

Continue

Posted by LongBridge Corporation on March 10, 2015 at 2:36am

Data sync between 2 servers

Say i have 2 servers named server01 and server02.
server01 is my database server and server02 for a backup server.
Now how can I make a connection to save all data server01 to server02(only database data)
Note: both servers are connected to a LAN network.

Posted by Shakaouth Shanto on March 2, 2015 at 9:17am — 1 Comment

© 2015   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service