The social network for Oracle people
order by clause
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: 145
Replies to This Discussion
-
Permalink Reply by Eddie Awad on -
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.
-
Permalink Reply by Suresh Kp on -
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?
-
Permalink Reply by Eddie Awad on
-
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
© 2013 Created by Eddie Awad.
