very cool stuff... I even modified the code to be even more random and could not make it crash the way I intended. Problem is, my SQL is EXACTLY the same as what we are playing with in our examples and it still gives me the same garbage. I even modified the field types to be exactly the same as mine; DOUBLEs and DATETIMEs.

I have rebuilt the table on my c: drive without any connection to a DBC or any indexes. I have appended the original data into the file. Execute the SQL statement with an order by on the Patient ID, so that I can see 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. What I actually see is 2, 4, 5, 7, 9, and 10.


Try this... :-)

* pcode added for extra fields to validate to
CREATE TABLE c:\patadd (patient_id B(0), patadd_id B(0), ef_date T, pcode C(10))

*Copy the following to a text file: c:\output.txt

1,1,04/01/1997 00:20:00,"T0G0P0"
1,2,05/20/2000 00:02:00,"T0A2W0"
2,3,01/22/2002 07:51:00,"T7P1R6"
3,4,01/14/2002 08:06:00,"T7P2P6"
4,5,01/22/2000 20:25:00,"T7P2P4"
5,6,01/05/1999 09:13:00,"T7P1Y6"
5,7,10/22/2001 09:00:00,"T7P1Y2"
6,8,01/24/2002 07:43:00,"T7P1J6"
7,9,04/01/1997 08:45:00,"T7P2P5"
7,10,05/20/1997 23:14:00,"T7P2P5"
8,11,07/29/2001 10:32:00,"T7P2E2"
9,12,12/27/1999 10:06:00,"T0G0Z0"
10,13,09/29/2000 17:35:00,"T7P1E5"

select patadd
append from c:\output delimited


* This is the exact SQL statement (same as yours)

select * from c:\patadd t1 ;
where t1.ef_date == (select max(t2.ef_date) from c:\patadd t2 ;
where t1.patient_id = t2.patient_id) ;
order by t1.patient_id


Enjoy! I didn't :-)