Group By SQL question

[medwards]

As I've discovered, when using Group By and Max() you are not allowed to select columns if those columns do not appear in either the Group By clause or the Max() function. Is there another way to do this? I need the ID number of the records where the Max occurs. For example, this is not allowed:

SELECT RecordID, SSN, MAX(SomeDate) AS MaxDate
FROM TableA
GROUP BY SSN

but this is:

SELECT SSN, MAX(SomeDate) AS MaxDate
FROM TableA
GROUP BY SSN

So how do I get the ID number of the rows that have the max date for each SSN?

Thanks

 

[Castor66]

Perhaps this belongs in the SQL Server: Programming or the MS Access forums?

 

[medwards]

yes it does. accidentally posted it to the wrong forum... sorry