>>

>>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? :-))))))

Your query will return all latest orders then as you use INNER JOIN with max date per order.

If we need to return records with all latest orders we can also use this query:

select C.*, O.* from Customers C INNER JOIN ORDERS O on C.CustNo = O.CustNO
where O.OrderDate >= (select max(OrderDate) from Orders O1 where O1.CustNo = C.CustNo)