selecting the last record in a series of groups

<i><b>Originally posted by : Lee Farrant (leefarrant@hotmail.com)</b></i><br />hi, <br /><br />could someone help be right some sql as i am having problems at the moment.<br /><br />This is the example <br /><br />I have 5 fields <br /><br />FIELD1 FIELD2 FIELD3 FIELD4 FIELD5<br /><br />The data looks like this <br /><br />id FIELD1 FIELD2 FIELD3 FIELD4 FIELD5<br />1 cust1 task1 otherdata otherdata updateddate<br />2 cust1 task2 otherdata otherdata updateddate<br />3 cust1 task3 otherdata otherdata updateddate<br />4 cust2 task1 otherdata otherdata updateddate<br />5 cust2 task2 otherdata otherdata updateddate<br /><br />this is a 1 table database. people add new records then they have finished a task on the customer project.<br /><br />i have to right sql which will display all 5 fields BUT only display records 3 and 5 because this is the last update/task added for this customer.<br /><br />I starting using .last , though it was working then when the database began to fill up it never gave me the last recorded add by customer FIELD1.<br /><br />database is ms access<br /><br /><br />Thanks<br /><br />Lee <br />leefarrant@hotmail.com

Since in your example we can sort on FIELD2 (but I'm sure with real data it will be different)

SELECT Field1, Max(Field2), Field3, Field4, Field5
FROM Table
GROUP BY ID

Instead of Max(FIELD2) you would probably want to use some other field that indocates that this was the last task entered, like a date or so...

MArtin