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


Borislav - The above errors on the group by clause.
Naomi - Your version errors with a message that says queries of this type are not supported.

After thinking about this some more I realized I don't really need anything from the parent table. Everything I need can be had from the child table. Just 3 fields CUSTID, _YEAR, and AMOUNT, where I select only the most recent year. This should simplify it a bit. It should look something like:

select custid, max(_year), amount ;
from orders ;
group by custid 

This seems to work with the first 2 fields but fails when I add the third even when I tweak the GROUP BY clause.