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

Thanks, I'll give that a try.