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

Not exactly. The prupose of the query was to group records for all customers (returning one particular record for each one). It's old trick: group retains the last record, so data should be sorted first.