Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Help with SQL Syntax
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00630077
Message ID:
00630154
Views:
16
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
>
>
*Obtain contacts
>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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform