Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Last 3 orders from each customer - sql puzzle
Message
De
29/09/1999 17:06:03
 
 
À
24/09/1999 15:00:21
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00268263
Message ID:
00270765
Vues:
31
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
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform