It's not a secret anymore!*
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 OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA 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: 2053
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: 2053
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: 1671
    
  14

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