Select the max date and then select the last date for every record in a group since the max date

Question

I want to get the last date in the table (12/3/09) and show each Well in a Field, since the last date.

Field, Well, TestDate, Amount
X, A, 12/1/09, 500
Y, D, 12/1/09, 400
Y, E, 12/1/09, 300
Y, F, 12/2/09, 50
X, B, 12/2/09, 40
Z, G, 12/2/09, 30
X, C, 12/3/09, 512
Y, D, 12/3/09, 425
Z, G, 12/3/09, 31

SELECT Field, Well, Amount, Last(Date) as LastDate
    FROM table1
    GROUP BY Field, Well, Amount
    ORDER BY Last(Date), Field, Well

Yields this:

X, C, 12/3/09, 512
Y, D, 12/3/09, 425
Z, G, 12/3/09, 31

The desired result:

X, A, 12/1/09, 500
X, B, 12/2/09, 40
X, C, 12/3/09, 512
Y, D, 12/3/09, 425
Y, E, 12/1/09, 300
Y, F, 12/2/09, 50
Z, G, 12/3/09, 31

Any help is very appreciated.

Answer 1

One option: Get the date you want from a subquery and join it back to the main table:

SELECT Field, Well, LastDate, Amount from table1 t1
join (
 SELECT MAX(Date) as LastDate From Table1 Group by Well
) t2 on t1.Date = t2.LastDate