I am trying to find information on how DB2 (and Informix, since we are switching from Informix to DB2) breaks ties and returns data using the following type of select statement:

select max(date(claim_status_hist.claim_status_dt))
from claim_status_hist
where claim_status_cd = 1;

The specific is that although in this example claim_status_dt is of data type TIMESTAMP, using the date function ignores the time portion of the timestamp and only reads out the date. If you have two rows of claim_status_cd = 1 which have the same date and only differ by time, how does DB2 decide which one to return when using the MAX function before the DATE function?

I understand that we should be using max(timestamp) rather than max(date), but we are dealing with legacy code here. DB2 (and informix) both return a single date, but we are unable to figure out how each system decides which row to return. Informix does it one way, and DB2 seems to do it another way. Any help with this would be greatly appreciated.

Unfortunately, I can't say which version exactly of DB2 or Informix we are using, since I have no DBA access to the machine. I know we are using Fixpack 12 on AIX if that helps.

Thanks!

I'm not sure how you identified which record the database is returning ..

to the best of my knowledge, when you request a max of a function (date, in this case), the function is evaluated and the result rows are sorted. From the sorted records, the max is pulled out ..

This means, that any of the records with the maximum date may be the first one ... In any case, does that matter at all ????

Sathyaram

Thanks for the quick response. Unfortunately, it does matter. Without going into too many details, lets say we have a claim and we are trying to find the latest status for that claim by date. Right now, if the claim has its status changed on the same day we are unable to determine which is the actual latest claim status, because the code currently being used is max(date) rather than max(timestamp). DB2 returns one of the claim status codes, but we can't figure out why it picks one over the other. Informix does the same thing, but Informix does not appear to pick the same one. The databases appear to be using different tie breaker criteria, and we can't figure out what that is.

The reason we are hoping to get this information is because the effort to change the code to max(timestamp) is too great for us to take on right now due to a pending deadline. If we can better understand the criteria by which each system chooses one max date over another, it would be very helpful.

Thanks!