An example:

MailingListId.. ListSelectId.. StateCountAsOf.. StateCountId
1.............................. d/c................. 1/1/98......................1
1.............................. d/c................. 3/1/98......................2
1.............................. d/c................. 2/1/98......................3
2.............................. d/c................. 6/1/98......................4
2.............................. d/c................. 1/1/98......................5

d/c = Dont Care

I have two lists here MailingListId 1 & 2, The StateCountId is unique for each record

I want to get one record for each MailingListId with the most up to date StateCountAsOf

Those records would be StateCountId 2 & 4 above. I was hoping to do this all in SQL


>Then, I'm not sure I understand your question.
>
>> I want all of the newest records and ignore older matching records
>
>Please define "newest records" (since a given date or since the last query?).
>
>And what do you mean by "matching records"? Are two or more tables involved?
>
>>Thanks for the idea, but not quite what I had in mind. I want all of the newest records and ignore older matching records (matching on MailingListId and ListSelectId)
>>
>>>SELECT TOP 10 MailingListId, ListSelectId, ;
>>> StateCountAsOF, ;
>>> StateCountId ;
>>> FROM StateCount ;
>>> INTO CURSOR zStateCount;
>>> ORDER BY StateCountAsOf DESC
>>>
>>>HTH
>>>
>>>>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).