I shortened the names to 10 chars, but this approach seems to work:

SELECT CTalentId + DTOC( Dappoint) AS Key_, cStatus ;
FROM TalentAppointments ;
WHERE cStatus = "SUBMITTED" AND ;
CTalentId + DTOC( Dappoint) IN ;
( SELECT MAX( CTalentId + DTOC( Dappoint) ) ;
FROM TalentAppointments ;
GROUP BY CTalentId ;
)

There may be issues if you can have multiple records for same talent on same day.

I realize now the approach we both tried would not work because when using the statistical SQL functions, you never get an aligned independent field, rather only the GROUP BY field and the calculated statistic. If it had been a SUM() we would have realized that. Something abotu MIN and MAX makes you think you would get the real record. But that could not ever be--think about the situation where 2 or more records has the same max or min value.

-- Randy



>>>Yes. In fact that is the exact sytax I used and I was mystified when it didn't work.
>>
>>???????
>>
>>I'm stumped then, but I could swear I've used that exact approach before.
>
>Ok I created a table called
>TalentAppointments with 3 fields
>
>dAppointment D
>cProjectStatus C(10)
>cTalentId C(9)
>
>I've added 3 records.
>{01/02/99}, "SUBMITTED", 1
>{01/01/99}, "CALL BACK", 1
>{01/02/99}, "AUDITION", 2
>
>The select statement is as follows
>

>SELECT MAX( dAppointment ), cTalentId, cProjectStatus ;
>     FROM TalentAppointments ;
>     GROUP BY 2
>     HAVING cProjectStatus = "SUBMITTED"
>
>

>
>The return set was empty.
>
>I removed the HAVING clause and the return set was as follows.
>{01/02/99}, "CALL BACK", "1"
>{01/02/99}, "AUDITION", "1"
>
>The first record should show "SUBMITTED" but its as if the value is randomly being pulled.