Re: Query sorting without Order by

Whether it is true or not with MS SQL is irrelevant. You are using Oracle not MS SQL server. In MS SQL server the concurrency model is different (less flexible) than Oracle - should we change that too? But I digress.

I believe if you look at the standard (and even if you call MS) you will find that unless you specify how you want to get the results back (via an order by) then you cannot be guaranteed on the order that you get back even being consistent from query to query. So the project leader is relying on something that is very shaky and could change at any time. In fact MS might decide to change it with a service pack or an upgrade of their database and not even tell you they did it. Why? You are not supposed to rely on such behaviors. It is poor programming practice and hurts maintainability to do so. Relying on a quirk means that when it breaks (when MS changes things) then you have no recourse but to change your code because what they changed would not be introducing a bug.

Often database optimizers, the things that decide how to retrieve your data, change strategies. In fact they may change strategies based upon how much data you have in the database or how much data you are requesting. It also might change based upon when you analyzed the table last or what indexes are or are not there. In Oracle you can put hints etc. and you can store the data in any one of several ways. I saw a fascinating analysis a couple of years ago on this board demonstrating this.

It sounds like the project lead wanted to use database X and now has to use database Y and if that is the case you really can't win.

Jim
"Putrasto" <putasto_at_mmedia-i.com> wrote in message news:HMCf6.13435$K6.105696_at_news...
> Hallo Jim
>
> Thank you for your advice.
> Well, actually I already have belief like yours.
> But my friend (Project leader) insist that
> in MS SQL that thing can be done (manipulating index), and the result
 always
> true (already sorted).
> And he said this process will be quicker than if we use order by .
> I can not disprove/prove because i don't have ms sql.
> any clue ?
>
> Thank you for your attention.
>
>
>
> Jim Kennedy <kennedy-family_at_home.com> wrote in message
> news:2aqf6.376288$U46.11115697_at_news1.sttls1.wa.home.com...
> > Without order by (in any database) the results are unspecified as to
 what
> > order they come back in. It maybe in some cases that the rows come back
 in
> > the order you desire, however, that may not always be true. Why don't
 you
> > want to specify what order you want the data in?
> > Jim
> >
> > "Putrasto" <putasto_at_mmedia-i.com> wrote in message
> > news:qpCf6.13434$K6.105746_at_news...
> > > Hallo all...
> > >
> > > I want to make sql query (in stored procedure) to select rows,
> > > and i want the selected rows automatically ordered (ascending or
> > > descending),
> > > without keyword 'order by' in the query .
> > > Is that possible in oracle?
> > > my friends told me that he ever done that in MS SQL using index
> > > in its table (design).
> > >
> > > Thank in advance.
> > >
> > >
> >
> >
>
>
Received on Mon Feb 05 2001 - 00:56:32 CST