Select top 5 from each country

[Assamita]

Hi,
I have an excel file with sales order amounts for different customers from different countries. That's a linked table to an access, where I've been able to create a query that sums up the amount for each customer in each country, sorted first by country, then by amount.
How can now create a query that only retreives the top 5 customers for each country? If I could just modify the existing query, that would be great, but if I need a new query linked to the first one it's ok too. I just made an SQL query like this:

SELECT TOP 5 BW_top5customersbyinvoice.[Company code Name], BW_top5customersbyinvoice.[Sold-to party], BW_top5customersbyinvoice.[Sold-to party Name], BW_top5customersbyinvoice.[SumOfNet Sell Price Invoice]
FROM BW_top5customersbyinvoice;

But this only shows the first 5 rows of the query BW_top5customersbyinvoice (which is the one I was talking earlier).

Any suggestion?



Thank you

[orange]

Here is an article by Allen Browne re subqueries. Review the section on TOP N


Good luck.

[Assamita]

thank you orange. I've read the article, but I can't figure it out to work with my file in particular. Anyway, since I have to export this to excel anyway, I've seen I can do it with a pivot table.
Thank you anyway.

[orange]

This is his generic SQL. The subquery is in blue.

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

[alansidman]

This link should show you specifically how to do that

http://www.datapigtechnologies.com/f...spergroup.html