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:

Group	Level	ContractDate	Salary
A	1	01/04/2001	10000
A	1	01/04/2002	11000
A	2	01/04/2001	20000
A	2	01/04/2002	21000
B	1	01/06/2001	10000
B	1	01/06/2002	11000

<tbody>
</tbody>

I'm trying to get the following results:

Group	Level	ContractDate	Salary
A	1	01/04/2002	11000
A	2	01/04/2002	21000
B	1	01/06/2002	11000

<tbody>
</tbody>

 

[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"