>>>>Hi All -
>>>>
>>>>I am experiencing a bit of brain deadness this morning. I set out to write what I thought would be a rather simple SQL select statement but I just can't seem to get my head around it. Thus my plea for help. There are 2 tables CUSTOMERS and ORDERS. Common field is custID. I want to select all customers and the _date and amount of their most recent order (one record per customer). Can someone help me figure out how to do this?
>>>>
>>>>Thanks
>>>
>>>What DB you use?
>>>VFP - what version?
>>>SQL Server - what version?
>>>Other?
>>
>>
>>Using VFP:
>>

>>select Cust.*, Ord.Amount. Ord.Date from Customers Cust INNER JOIN Orders Ord on Cust.CustNo = Ord.CustNo ;
>>where Ord.OrderID = (select top 1 OrderID from Orders O where O.CustNo = Cust.CustNo ORDER BY OrderDate DESC)

>>
>>This is one of the possible variations of this query.
>
>Packed solution:
>

>select Cust.*, LO.*LastDate, cast(RIGHT(LO.DateAmount,100) as N(12,4)) as Amount from Customers Cust ;
>
>INNER JOIN (select CustNo, max(Date) as LastDate, ;
>max(cast(date as C(10)) + cast(Amount as C(100))) as DateAmount ;
>from Orders group by CustNo) LO on Cust.CustNo = LO.CustNo
>

>
>Should perform better than other alternatives.



But where you sum the whole amount for the order?
One order can many rows, not just one :-)


And BTW we both assume that one customer can have one order for a date, but what if that customer has 2 or 3? :-))))))