>>>I want to make a file containing the last three orders for each customer.
>>>
>>>For example, in TasTraders, I can see all customer orders with:
>>>
>>>SELECT Customer.customer_id, Orders.order_id, Orders.order_date;
>>> FROM tastrade!orders INNER JOIN tastrade!customer ;
>>> ON Orders.customer_id = Customer.customer_id;
>>> ORDER BY Customer.customer_id, Orders.order_date DESC
>>>
>>>But I want only the first three records for each customer. If I say
>>>
>>>SELECT TOP 3 Customer.customer_id, Orders.order_id, Orders.order_date;
>>> FROM tastrade!orders INNER JOIN tastrade!customer ;
>>> ON Orders.customer_id = Customer.customer_id;
>>> ORDER BY Customer.customer_id, Orders.order_date DESC
>>>
>>>I get the first three records only, not three for each customer.
>>>I think this calls for a nested sort. Has anyone devised a way?
>>
>>Have you tried to GROUP BY customer.customer_id
>>
>>Wayne
>
>GROUP BY doesn't help. I needed to do this several months ago and did it by looping through the customers, selecting the top 3 orders, and appending that to my cumulative table. I also had to add an additional record which was the "all other".

I can't promise that this is the fastest or simplest way, but it should work. First, get your orders in order so you can take the top three from each group:

SELECT orders.customer_id, Orders.order_id, Orders.order_date FROM tastrade!orders INTO CURSOR selorders ORDER BY orders.customer_id, Orders.order_date

Then number them:

SELECT *, RECNO() AS recnum FROM selorders INTO CURSOR numorders

Then make a query that will identify the last order for each customer with its identifying recnum:

SELECT customer_id, order_id, recnum FROM numorders INTO CURSOR grouporders GROUP BY customer_id

Since order_id and recnum are not included in the GROUP BY clause, it takes them from the last record in each group, kind of like the LAST() aggregate function you would be using in Access97.

Now you can use recnum from grouporders and the last three recnums from numorders to give you the last three orders for each customer:

SELECT numorders.customer_id, numorders.order_id, numorders_order_date FROM numorders INNER JOIN grouporders ON numorders.customer_id = grouporders.customer_id AND numorders.recnum < grouporders.recnum - 3 INTO CURSOR threeorders ORDER BY 1,3

The non-equi-join will be a bit slow.

You can now join threeorders back to your customer table if you like.