Re: ROWNUM is out of order when ORDER BY clause used

>A user is trying to select records from a table in a specific order
>and would also like to number the rows retrieved using ROWNUM.
>Unfortunately, when he orders by a column, the ROWNUM is out of order.
>Is there some way to keep the sequence of ROWNUM and order by a table
>column?

ROWNUM reflect the order of retrieval not the sorted order.

If you have an index that reflects the order you are interested in you can force its use with an appropriate where clause. Eg to force retreival sorted by empno;

select ...
from emp
where empno > 0;

You can also use a hint to suggest the use of an index. But be warned that hints are just what they say they are and the optomizer have choose to go it own way.

David Roth

CMC Systems                                       All opinions expressed
UNIX Client/Server System Integrators      are my own.