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