Query on Max date then Max Amount

[kayek]

I am trying to figure out how to write a query that groups on Max Date then Max Amount. Below is example table and what I want for results.

Table1
AcctNum Thing Date Amount
1234 Cat 1/1/05 200.00
1234 Dog 1/1/05 300.00
1234 Fish 5/2/04 200.61
1234 Bird 3/1/05 600.51
4321 Dog 2/9/05 200.55
4321 Fish 2/9/05 300.99
4321 Worm 5/1/04 200.25
4321 Pig 3/1/05 600.01

Results Want
1234 Dog 1/1/05 300.00
4321 Dog 2/9/05 200.55

 

[Golom]

Select AcctNum, Thing, MAX([Date]) As MaxDate, MAX(Amount) As MaxAmount

From myTable

Group By AcctNum, Thing

 

[PHV]

SELECT M.AcctNum, M.Thing, M.Date, M.Amount
FROM tblAmount AS M INNER JOIN (
SELECT X.AcctNum, X.Date, Max(Amount) AS MaxAmount
FROM tblAmount AS X INNER JOIN (
SELECT AcctNum, Max([Date]) AS MaxDate FROM tblAmount GROUP BY AcctNum
) AS D ON (X.Date = D.MaxDate) AND (X.AcctNum = D.AcctNum)
GROUP BY X.AcctNum, X.Date
) AS A ON M.AcctNum = A.AcctNum AND M.Date = A.Date AND M.Amount = A.MaxAmount


Results:[tt]
1234 Bird 3/1/05 600.51
4321 Fish 2/9/05 300.99[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kayek]

It worked Thanks for taking the time to help me figure this out!!!

 

[PHV]

It worked
Which ?