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