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


Here is a way to do it in a single select statement:

Select max(date_time) as maxdate, * ;
from MyTable ;
group by ID ;
having date_time = maxdate

Steve