Yeah, I did actually tried what you've just suggested, and it basically ran forever.
Kev
>In case like that you have to use correlated subquery to determine wich record in the group is the one we need. Here's a draft for such subquery.
...
> AND DTOS(re_date) + re_time IN ( SELECT DTOS(re_date) + MIN(re_time) ;
> FROM Referral RF ;
> WHERE RF.re_date = Referral.re_date AND RF.Cl_Ref = Referral.Cl_Ref ;
> GROUP BY 1) ;
>...
Unfortunatelly VFP support for subqueries is very limited. I'm afraid that this subquery wouldn't run in VFP.
>
>>I'm beginning to think that this can't be achieved in 1 single Select statement, either a 2nd query or a sub-query must be required.
>>
>>Kev
>>
>>>In VFP TOP clause requires Order BY that determines what columns are taken in account by Top clause. It wouldn't help in this case because it'll return 1 records per query not per group.
>>>
>>>>Try Select Top 1 Referral.* instead of your Select Referral.*. This *should* return the earliest (I'm not 100% on whether Top 1 is selected before or after the Order clause).
>>>>
>>>>>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