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

aahhh.. I was thinking he only needed it for one customer at a time....