>>>I am trying to figure out the best way to build a cursor with a select statement that returns all of the fields in a table grouped by one field that has the most recent date and time in a datetime field. For example if I have:
>>>
>>> ID Status Date/time
>>> 1 A 02/01/1999 10:00 AM
>>> 2 B 02/01/1999 11:00 AM
>>> 1 C 02/01/1999 01:00 PM
>>> 3 D 02/02/1999 08:00 AM
>>> 2 F 02/02/1999 11:00 AM
>>> 1 G 02/01/1999 10:30 AM
>>> 3 H 02/01/1999 09:00 AM
>>>
>>>I want the result cursor to be:
>>>
>>> ID Status Date/time
>>> 1 C 02/01/1999 01:00 PM
>>> 2 F 02/02/1999 11:00 AM
>>> 3 D 02/02/1999 08:00 AM
>>>
>>>I have tried using Max(date_time) and grouping by the ID but that returns the fields from the first record found for the ID and then the max date_time.
>>>
>>>I am using this cursor to build a list on a form, so speed is important.
>>>
>>>Any help or hints will be greatly appreciated.
>>
>>Select * From Mytable into cursor tmp1 nofilter Order by id,datetime
>>Select * From tmp1 into cursor tmp2 group by id
>
>
>Thanks for the help. Does anyone know if there is a way to do this with one select statement? If not, this will work.

SELECT * FROM MyTABLE a ;
WHERE STR(a.ID) + TTOC(date_time, 1) = ( ;
SELECT MAX(STR(b.ID) + TTOC(date_time, 1)) ;
FROM Mytable b ;
GROUP BY ID)

This works.