>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
Edward Pikman
Independent Consultant