Re: Query sorting without Order by

ditto to the previous posters...

An Oracle SELECT statement is not guaranteed to return rows in any particular order from a query unless an ORDER BY clause is included.

There are a few tricks you can use, as indicated by the previous posters. For example, the current implementation of GROUP BY will return rows in sorted order, but this is a side effect, not a guarantee. The same is true for the DISTINCT keyword and using hints.

If you need the rows returned in a sorted order, use the SORT BY clause. In the cases where the SORT BY clause is not allowed (for example, an "INSERT INTO ... SELECT ... FROM ... " statement, use GROUP BY instead.

HTH "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote in message news:96hps8$eir$1_at_spiney.sierra.com...
> what is so nasty about using the words ORDER BY?
>
> Oracle, when encountering the order by clause does a sort before returning
> the data.
>
> Your query does a sort (to determine distinct) before returning the data.
>
> In order to get the data in sorted order, you must either use some form of
> sorting
> or a HINT on a previously sorted index.
>
>
> "Vasily Golikov" <vvg_at_baltros.ru> wrote in message
> news:3a7ebfec_at_ns.baltros.ru...
> > Hello
> > I usually use the following trick
> >
> > select distinct (field_for_sort, table_id) from table_x
> >
> > This example sorts the set by field field_for_sort. In this case you can
 do
> > rows only in the acsending order.
> >
> > best regards,
> > Vasily Golikov
> >
> >
> >
> > > 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 Thu Feb 22 2001 - 08:29:59 CST