It appears to us that SQL Selects involving DateTimes in Join conditions produce inconsistent answers depending upon the data. We are attempting to extract the latest transaction DateTime for a given transaction type in a table. The following simplified data table and code will reproduce the problem.

Please run the following code repeatedly and observe the results in crWrongAnswer. It should always have three rows, but you may observe anything from zero to three rows.

** Begin Code
CREATE TABLE DT (DTID I, FKID I, DateTime T)
SELECT DT
INSERT INTO DT (DTID, FKID, DateTime) VALUES (1, 4, DATETIME()-INT(RAND()*5555555))
INSERT INTO DT (DTID, FKID, DateTime) VALUES (2, 5, DATETIME()-INT(RAND()*5555555))
INSERT INTO DT (DTID, FKID, DateTime) VALUES (3, 6, DATETIME()-INT(RAND()*5555555))
BROWSE LAST NOWAIT
SELECT FKID, MAX(DateTime) AS DateTime FROM DT GROUP BY 1 INTO CURSOR crMaxDT NOFILTER
BROWSE LAST NOWAIT
SELECT DT.* FROM DT INNER JOIN crMaxDT ON (DT.FKID = crMaxDT.FKID and DT.DateTime = crMaxDT.DateTime) INTO CURSOR crWrongAnswer NOFILTER
BROWSE LAST NOWAIT
** End Code

This is driving us crazy in Virginia. Please help!

Thank you,
John L. Malinowski
Wicks Broadcast Solutions