General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>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".
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only