Bret
See my last message (posted a few minutes ago to Sergey).
Thanks
Kev
>Make a NOFILTER query that sorts the data the way you want, with Re_Time DESCENDING. Then select from that query, and leave the Re_Time out of the GROUP BY clause. It will select the Re_Time values from the last record in each group, which will be the smallest value if you sorted Re_Time DESCENDING. The ORDER BY clause in the second query doesn't matter.
>
>BTW, you wouldn't do it this way in some other tools such as Access. Other aggregate functions such as MAX and MIN are available, and fields without an aggregate function that aren't in the GROUP BY clause are forbidden.
>
>>Hi
>>
>>I need confirmation on this, can anyone advise?
>>
>>There may be cases in the
Referral Table where there may be 2> entries for the same Date (
Re_Date) for a Client (
Cl_Ref). I want this select to pick up the earliest time'd (
Re_Time) Referral if there is more than 1 record for the same Date.
>>
>>Hope the above is clear!! Here is my code
>>
>>
>>SELECT Referral.*;
>> FROM Referral;
>> INNER JOIN Client;
>> ON Referral.Cl_Ref = Client.Cl_Ref;
>> WHERE BETWEEN(DTOS(Re_Date), DTOS(gdStartDate), DTOS(gdEndDate));
>> AND Re_Message = "R";
>> AND Re_Error <> "Y";
>> AND Re_Nfa <> "Y";
>> ORDER BY Re_Time;
>> GROUP BY Referral.Cl_Ref, Re_Date
>>
>>
>>
>>Will the above Select do this for me?
>>
>>Thanks
>>Kev