>>>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?
>
>
>Hi Borislav - It's VFP 9.
SELECT Customers.*,;
Tbl1.Date,;
Tbl1.Amount;
FROM Customers;
INNER JOIN (SELECT Orders.CustId,;
Orders.Date,;
SUM(Orders.Amount) AS Amount;
FROM Orders;
INNER JOIN (SELECT CustId, MAX(Date) AS Date;
FROM Orders;
GROUP BY CustId) TblMaxdate;
ON Orders.CustId = TblMaxdate.CustId AND;
Orders.Date = TblMaxdate.Date;
GROUP BY Orders.CustId, Orders.Date) Tbl1
ON Customers.CustId = Tbl1.CustId
Not tested!
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.