>>I need help in selecting records from the following table (simplified):
>>
>>CUST_ID
>>ORD_DATE
>>ORD_TIME
>>
>>For a given date (ORD_DATE), I need to select one order record per customer (CUST_ID) based on the time of order (ORD_TIME). For example, the Order Time should be the latest of the day.
>>
>
>While you can do this with one query in VFP 8 and earlier using a subquery, in VFP 9, it's much easier:
>
>SELECT Orders.Cust_ID, Orders.Ord_Date, Orders.Ord_Time, ;
> <other fields>
> FROM Orders ;
> JOIN (SELECT Cust_ID, Ord_Date, ;
> MAX(Ord_Time) AS Max_Time;
> FROM Orders OrdLast ;
> GROUP BY 1, 2) LastOrder ;
> ON Orders.Cust_ID = Last_Order.Cust_ID ;
> AND Orders.Ord_Date = Last_Order.Ord_Date ;
> AND Orders.Ord_Time = Last_Order.Max_Time
>
>
>My testing shows this approach to be faster than any of the others.
>
>Tamar
Thank you for this code. I will save it in VFP 9 folder until for the future use (I don't have VFP 9 yet).
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham