>>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 folks.
> I reexamined my contribution, and while the explanation is correct, the solution is wrong. My solution would not get the correct price information. It turns out that a correlated subquery is required:
>
> SELECT Cust, Date, Price FROM Test t1;
> WHERE Date IN(SELECT MAX(Date) FROM Test t2 WHERE t1.Cust == t2.Cust)
>
>(Thanks to my colleague Wes Anderson for reminding me that tables can be aliased in VFP queries)
>Note: If there is more than one record on the last date, multiple records will be returned. Perhaps a time field would help.

You may wanna take a look to Ed's and Robert's solutions. They use only 2 selects. Yours rebuilds the inner SELECT for each record. So, you end with N+1 SELECTs (where N = the number of customers).

Vlad