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

It seems to me that your index is corrupted. Try:
analyze table CLASS.EDL_RENTAL_AMENDMENT validate structure cascade; (not analyze index because it doesn't verify the consistence between data in index and table).
or drop and recreate the index (not alter index rebuild because it uses the current index to rebuild).

Then, do you have the same result?

Glenn Heinze a écrit dans le message <7r5q5m$nrv$1_at_cougar.golden.net>...
>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
> )
>
>
>
Received on Wed Sep 08 1999 - 11:10:08 CDT