This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResultSet not in Correct Order

 
jason zepris
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2068
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2068
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic