>>>Can anyone tell me if the following can be done in a one line select statement. I cannot seem to get the results I require.
>>>
>>>I have one table with the following fields: Customer, Date, Price. I am simply trying to get the last price quoted to the customer.
>>>
>>>CustA, 01/01/01, 100
>>>CustA, 01/01/97, 111
>>>CustB, 02/02/96, 200
>>>CustB, 02/02/02., 222
>>>
>>>I want my result to be:
>>>
>>>CustA, 01/01/97, 111
>>>CustB, 02/02/96, 200
>>>
>>>If I use SELECT * FROM myTable ORDER BY Date DESC GROUP BY Customer
>>>I get ...
>>>CustA, 01/01/01, 100
>>>CustB, 02/02/01, 222
>>>
>>>Again, I am looking for the last price quoted only.
>>>
>>>Any help would be greatly appreciated.
>>>
>>>-Isaac Roda
>>> Hi, Isaac:
>> First the solution to your problem:
>>
>> SELECT Cust, MAX(Date), Price FROM myTable ;
>> GROUP BY Cust
>>
>> Second the reason for the results of your initial attempt:
>> You told VFP to get all rows of your table:
>> 1) SQL chooses the order that output rows will be generated in for optimization purposes, i.e. order of initial output will vary
>> Then to take each group of rows for the same customer and choose one record from each group(basically randomly)
>> 2) VFP had no criteria as to which row it should choose
>> Finally to order the result by date.
>>
>>SQL in general works as follows:
>> (Perform Joins)
>> Apply all filter conditions (WHERE)
>> Apply all post filter conditions (HAVING)
>> Order result for presentation
>>
>>Your order by date occurred too late to have any effect on the output set.
>>P.S. I didn't see any effect from SET CENTURY but you might want to check.
>
>I think this works only if the last record for each group is the last one physically in the dbf. And even so, I'm not sure you can use it safely.
>
>Am I right to say that nothing warrants that the selected price will be the last one (in the date order)?
>
>Vlad
You're absolutely right. I needed to use a correlated subquery which I tested and included in my second message.
Thanks,
Ned