Re: Returning results in the order they were entered

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:<2eqdnUt3Xt5yTTeiRVn-iQ_at_comcast.com>...
> you're going to have to record the date entered and then sort on that
>
> reason -- oracle can and will reorder the physical location of the rows
> (during normal operation and typical maintenance operations), so if you
> don't store a timestamp, so the order entered is not preserved
>
> --
> Mark C. Stock
> email mcstock -> enquery(dot)com
> www.enquery.com
> (888) 512-2048
>
>
> "Boffo Jinko" <test_at_yahoo.com> wrote in message
> news:boehfl$1e08bm$1_at_ID-147295.news.uni-berlin.de...
> > I have a query that returns the results of a basic select statement, like
> > so:
> >
> > select distinct id,name from Caseflow.cases where number1 in
> > (124125,124122,124130);
> >
> > When this query is returned, it returns the results in order from lowest
> to
> > highest. This is typically okay, but I need to have the results returned
> in
> > the order they were entered by the user. So in the above example, the
> > results would have to be returned at 124125,124122,124130, not
> > 124122,124125,124130 as they normally would be. Is there a way to do this,
> > or am I going to have to resolve this problem in my web page code? There
> are
> > no other fields in common that I could sort by - it has to be by the
> number
> > entered in the IN clause, and they are often not in any kind of order. And
> > no, telling the user to enter them differently isn't an option!
> >
> > Thank you,
> > Scott
> >

Scott, to add to what Mark said, according to relational theory definition a table is an unordered heap of data so the only way to guarantee the order of data in Oracle is to order it using the ORDER BY clause. In your case since you want physically inserted time order you either need a timestamp or a sequence number that the query can order on.

HTH -- Mark D Powell -- Received on Fri Nov 07 2003 - 07:45:18 CST