Query to return max date for multiple rows

[MPFraser7]

Hello,

I'm trying to query a table to return records with the latest contract date. I've tried a bunch of max date codes that I found on the forum but none of them seem to return what I'm looking for. Here's my table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Level[/TD]
[TD]ContractDate[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]01/04/2001[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]01/04/2002[/TD]
[TD]11000[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]01/04/2001[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]01/04/2002[/TD]
[TD]21000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]01/06/2001[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]01/06/2002[/TD]
[TD]11000[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to get the following results:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Level[/TD]
[TD]ContractDate[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]01/04/2002[/TD]
[TD]11000[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]01/04/2002[/TD]
[TD]21000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]01/06/2002[/TD]
[TD]11000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roxxien]

Hello,

here is what I would personally do to get the result that you want.

Subquery (I name it sub1)

Select table.Group, table.Level, Max(table.ContractDate) AS LastContractDate
From table
Group by table.Group, table.Level

Create a Join to add the Salary column

Select sub1.Group, sub1.Level, sub1.LastContratDate, table.Salary
From table Join sub1 on (sub1.Group = table.Group) and (sub1.Level = table.Level)
Order by sub1.Group, sub1.Level, sub1.LastContratDate, table.Salary

Edit : The table name is an example, I highly doubt that your table name is "table"