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

Ed, since the DESC clause was the problem (and I see he left a message to agree) I thought if he tried it both ways he'd understand WHY the DESC clause was wrong. Sometimes the teacher in me takes over.....

Barbara