Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>>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".
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement