• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

ResultSet not in Correct Order

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Something must be done about this. Let's start by reading this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic