Xref: alice comp.databases.oracle.server:65103 Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!newsfeed.direct.ca!news1.tor.metronet.ca!newsfeed2.golden.net!not-for-mail From: "Glenn Heinze" Newsgroups: comp.databases.oracle.server Subject: Re: Why are "order by ...desc" results different from "order by ... asc"? Date: Thu, 9 Sep 1999 07:41:10 -0400 Organization: Golden Triangle Online Lines: 117 Message-ID: <7r86qd$5v5$1@cougar.golden.net> References: <7r5q5m$nrv$1@cougar.golden.net> <37D63FF5.60CB@yahoo.com> X-Trace: cougar.golden.net 936877709 6117 209.183.150.98 (9 Sep 1999 11:48:29 GMT) X-Complaints-To: abuse@golden.net NNTP-Posting-Date: 9 Sep 1999 11:48:29 GMT X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Connor McDonald wrote in message <37D63FF5.60CB@yahoo.com>... >Glenn Heinze wrote: >> >> When selecting from a table with no "order by" clause, there are 2 rows >> returned. >> Same select with an "order by ... ASC" returns same two rows. >> Same select with an "order by ... DESC" returns 20 rows. >> If the index is dropped, 2 rows are returned in all cases. >> There is only one index on the table. >> Is something is wrong with index, or what am I missing?!! >> >> Thanks in advance for any help, >> -Glenn >> >> ================================== >> SQL> select rental_id, amendment_counter >> 2 from CLASS.EDL_RENTAL_AMENDMENT >> 3 where rental_id = 6200 >> 4 and amendment_counter > 0; >> >> RENTAL_ID AMENDMENT_COUNTER >> ---------- ----------------- >> 6200 1 >> 6200 2 >> >> ================================== >> SQL> select rental_id, amendment_counter >> 2 from CLASS.EDL_RENTAL_AMENDMENT >> 3 where rental_id = 6200 >> 4 and amendment_counter > 0 >> 5 order by amendment_counter asc; >> >> RENTAL_ID AMENDMENT_COUNTER >> ---------- ----------------- >> 6200 1 >> 6200 2 >> >> ================================== >> SQL> select rental_id, amendment_counter >> 2 from CLASS.EDL_RENTAL_AMENDMENT >> 3 where rental_id = 6200 >> 4 and amendment_counter > 0 >> 5 order by amendment_counter desc; >> >> RENTAL_ID AMENDMENT_COUNTER >> ---------- ----------------- >> 6294 1 >> 6292 1 >> 6287 1 >> 6279 1 >> 6273 8 >> 6273 7 >> 6273 6 >> 6273 5 >> 6273 4 >> 6273 3 >> 6273 2 >> >> RENTAL_ID AMENDMENT_COUNTER >> ---------- ----------------- >> 6273 1 >> 6272 1 >> 6266 1 >> 6265 1 >> 6261 1 >> 6224 1 >> 6210 1 >> 6200 2 >> 6200 1 >> >> 20 rows selected. >> >> ================================== >> SQL> select count(*) >> 2 from CLASS.EDL_RENTAL_AMENDMENT >> 3 where rental_id = 6200 >> 4 and amendment_counter > 0; >> >> COUNT(*) >> ---------- >> 2 >> >> ================================== >> CREATE UNIQUE INDEX class.xpkrental_amendment >> ON class.edl_rental_amendment >> ( rental_id, >> amendment_counter ) >> PCTFREE 10 >> INITRANS 2 >> MAXTRANS 255 >> TABLESPACE index_space >> STORAGE ( >> INITIAL 40960 >> NEXT 90112 >> PCTINCREASE 1 >> MINEXTENTS 1 >> MAXEXTENTS 505 >> ) > >What version are you on ? > >There were some bugs with 7.3.x when oracle chose to select from an >index in descending order... >-- >=========================================== >Connor McDonald >"These views mine, no-one elses etc etc" >connor_mcdonald@yahoo.com > >"Some days you're the pigeon, and some days you're the statue." --g--> You are right, we are using 7.3.3 So I guess we can't use the "order by ... DESC" clause. Thanks for your reply, it is appreciated -Glenn