>>>>>>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
>>>>>
>>>>>Sorry, it's two lines:
>>>>>select * from table1 into cursor tmp1 order by customer,date
>>>>>select * from tmp1 group by customer
>>>>
>>>>Ed:
>>>>
>>>>If the data field was a Date-Time field, do you think the following would work:
>>>>
>>>>Select Customer, max(Date), Price from Table1
>>>>
>>>Surely not, because it must be grouped. BTW, I have tested my answer, and I always do this before posting something here.
>>
>>I note that Ed Pikman's reply did NOT use the "DESC" in the SELECT. Isaac, try it both ways.
>>
>>Barbara
>
>Why both? One is enough. Group always retains the last record.

Isaac didn't mention if he was using VFP 5.0 or not, but isn't this what the 'TOP n' synatax is for?
'select top n * from table1 where customer = somecustomer order by date desc'