>>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