I'm trying to use the following SELECT to get me the most current record(s) from my table based on the DateTime field StateCountAsOf.
The field StateCountId is the Key field for each record.

SELECT MailingListId, ListSelectId, ;
MAX(StateCountAsOF) AS StateCountAsOF, ;
StateCountId ;
FROM StateCount ;
GROUP BY MailingListId, ListSelectId ;
INTO CURSOR zStateCount

When I run my SELECT I get the most current date but the field ListSelectId (which is a key into another table is for another record). I tried GROUP BY MAX(StateCountAsOf) and HAVING BY MAX(StateCountAsOf) but get syntax errors. I can't use StateCountId in my GROUP BY because I then get all records (since it is the key field).

Any Ideas?