Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why are "order by ...desc" results different from "order by ... asc"?

Re: Why are "order by ...desc" results different from "order by ... asc"?

From: Glenn Heinze <glenn.heinze_at_city.kitchener.on.ca>
Date: Thu, 9 Sep 1999 07:41:10 -0400
Message-ID: <7r86qd$5v5$1@cougar.golden.net>

Connor McDonald wrote in message <37D63FF5.60CB_at_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_at_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 Received on Thu Sep 09 1999 - 06:41:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US