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