Re: Issue with the order of data being retrieved from 10g.

Comments embedded.
On May 29, 10:10 am, neeraj.gupta1..._at_gmail.com wrote:
> Hi
>
> My problem is:
>
> i have a table say POLICY with three columns A, B and C.
> table has two rows, with column C having value 0 for both rows.
>
> Now i am writing
>
> select * from POLICY order by C.
>
> as both the rows has value of 0(zero), the result should be shorted
> assending based on the
> rowid,

I believe you mean 'sorted', and can you tell us WHY your data 'should be' sorted also by ROWID?

> but here its opposite, i.e. the result set is shorted
> descending for rowid.
>

Such is the way Oracle works. Variances abound between differing releases of the product.

> Is that an issue with the version of 10g server, i am using or Is it
> some settings of the oracle server?
>

Neither. It is an issue with your perception.

> Thanks
> Neeraj

You are, in a way, expecting Oracle to further order your results in a manner unspecified by you. Oracle has always cautioned against this, stating if you want your data ordered you need to use ORDER BY (which, of course, you are using). Your order by clause is on column C, not the ROWID. You can't expect Oracle, by default, to order your data the way YOU think it should be ordered. If you really want the results ordered by C and by the rowid then state this in your query:

select * from policy order by c, rowid;

David Fitzjarrell Received on Tue May 29 2007 - 10:40:16 CDT