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

Fast Eddie,

I hope you don't mind me calling you that, but in view of your recent UT award, it seems approproate (g). Congratulations.

This problem points out a potential "gotcha" with queries. If the records had been physically organized by date, then the following query would have worked fine:

SELECT *;
FROM Table1;
GROUP BY CUSTOMER;
ORDER BY ORDER_DATE

However, because Isaac pointed out that the they weren't, the above will return inaccurate results, regardless of existing indexes. It's just another instance where making assumptions can get you into trouble, since the above will take the last *PHYSICAL* record in the table, rather than any ordering indication to the contrary.

BTW, being the son of a musician, I'm wondering if there's any relation between your company and Better Music Insitute.

George