even if there would be a particular way in which the rows with the same value for your order by column are returned I wouldn't rely on it.
The order could change with changing data,new indexes, new Oracle versions, etc. and will simply be not guaranteed.
As far as I know this depends on some things, e.g. if an ordered index was used for this sort (maybe you've an ordered index on column A and B and you use Order By A, then (if the Optimizer decides to use the index) the result for rows with the same value for A will be sorted by B.
If no Index is used I think you will retrieve the rows in physical order in the tablespace. This means in no guaranteed order at all.
You can try this e.g. this way: insert a lot of rows in some testing table. Then perform your query. Delete a block of rows from the "middle" of your table, then insert new rows and perform your query again.
But as I said some times before: never rely on any order which you don't explicitly define in your order by clause. Because "results may vary"...
Joined: Jun 24, 2008
Thanks for the reply. . I understood the point.
Actually my requirement was to fetch the most recent record based on a specific date from an History Table.
In a such a scenario like same date contain more than one record , i should use some other order by clause as well, like a time stamp field that should be in table to specify the insertion time of a record.
select * from history_table where effective_date <= specific_date order by specific_date desc, timestamp_column desc . right ?
Joined: Feb 12, 2009
Yes, but doesn't just "order by timestamp_column desc" do the same thing?
Or are there two different dates stored in these columns?
In fact ordering by a column of type timestamp nearly eliminates your problem, maybe except for data inserted in batches (many rows at a time). What would elimnate the problem completely is a column coming from a sequence, then you could definitely sort by the order of creation of the row.
Joined: Jun 24, 2008
yes, different dates are stored in those columns , effective_date column is a date column only not a timestamp.
yes. sequence will be the perfect way .
effective_date <= specific_date order by effective_date desc, seq_id desc . rgt?