Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Last 3 orders from each customer - sql puzzle
Message
From
29/09/1999 17:06:03
 
 
To
24/09/1999 15:00:21
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00268263
Message ID:
00270765
Views:
32
Hi Tom,
Thanks. Your suggestion of a selfjoin and a helpful paper at http://www.networksplus.net/cschreiner/OutFoxed11.html addressed my situation.
Nice.
Using that example, here's the last three orders for each Customer:

SELECT C.customer_id, O1.order_id, O1.order_date ;
FROM tastrade!Customer C, tastrade!orders O1, tastrade!orders O2 ;
WHERE C.customer_id = O1.Customer_id AND C.Customer_id = O2.Customer_id ;
AND DTOS(O1.order_date) + O1.order_id <= DTOS(O2.Order_date) + O2.Order_id ;
INTO CURSOR Result ;
GROUP by C.Customer_id, o1.order_date, O1.Order_id ;
HAVING COUNT(*) <= 3 ;
ORDER BY O1.customer_id, O1.Order_date DESC
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform