my dog learned polymorphism*
The moose likes JDBC and the fly likes ResultSet not in Correct Order 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 "ResultSet not in Correct Order" Watch "ResultSet not in Correct Order" New topic
Author

ResultSet not in Correct Order

jason zepris
Greenhorn

Joined: Jun 25, 2011
Posts: 8
Hi,

So I'm having the following issue. I have the following query:


I also have a stored procedure with the same query as above. In my Java code I'm executing both the query and the stored procedure now I would expect that both will return the same data in the same order but this is not the case. For rows where the dates are exactly the same the query and the stored proc are not returning the data in the same order. Does anyone know why this could be happening?
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2049
I have a feeling that you are using a Date or timestamp in your database.

These have more data in it not just ccyymmdd, it has up to milliseconds if i am not wrong. view the data down to milliseconds using your toad or sqldeveloper, etc.

Those small details like milliseconds is considered by the database when you do 'Order'.
jason zepris
Greenhorn

Joined: Jun 25, 2011
Posts: 8
Thanks for the reply. So the database I'm using is Sybase and the particular column with the order by is a smalldatetime. Through Toad I can see that the date field time value is 12:00:00.

Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2049
You may be at the mercy of different implementations if you dont explicitly do your ordering. Since they are exactly the same, each implementation, whether in java or db may return different order.

Maybe add a field in your sql 'order' to be more explicitly, where the ordering will really be predictable.

Or post-process them to reorder after the query, e.g. using java, or in db script (if that is possible).
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1624
    
  13

Your ORDER BY does not completely order your data because you have multiple rows for some dates. For rows with the same date, it's as if you had no ORDER BY clause, because you have not told the query engine how to order the data within a given date, so there is no guarantee what order the rows will be returned in (this is how most relational databases work). The easiest solution is to add an extra column to the ORDER BY e.g. ORDER BY dt ASC, nbr. This should guarantee that records are always returned in the specified order.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet not in Correct Order
 
Similar Threads
Need help on Stored Procedure Call with CLOB as IN Parameters
large resultset connection times out
Stored Procedure inside HQL query?
Accessing multiple resultsets
Resultset order issue