Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Query regarding Order by clause in an SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query regarding Order by clause in an SQL" Watch "Query regarding Order by clause in an SQL" New topic
Author

Query regarding Order by clause in an SQL

Sreejith kayappuram
Ranch Hand

Joined: Jun 24, 2008
Posts: 35
Hi,
when we give an order by clause,
SELECT columns FROM tables WHERE predicates ORDER BY column ASC/DESC . and the records will be returned , sorted based on column value.

Consider one scenario like the column value is same for more than one records. So in what order will those records be returned, is there any particular way those records will be returned.


Thanks
Sreejith
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Sreejith,

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"...



John
Sreejith kayappuram
Ranch Hand

Joined: Jun 24, 2008
Posts: 35
Hi John,
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 ?



John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
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.

Sreejith kayappuram
Ranch Hand

Joined: Jun 24, 2008
Posts: 35
Hi John,
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?

Thanks a lot for the help
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Yes, I would try it this way.

You're welcome!

John
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Query regarding Order by clause in an SQL
 
Similar Threads
How to retrieve the first record in a database table
iBatis groupBy spoils SQL order by
AJAX In Practice: implementing pagination?
RecordComparator
Rowcount in Resultset