Annoying Query Problem

[mohsinhq]

I have a query which does a count of records for each particular customer for each country. (14 countries)

I need a second query to ONLY take the top count for each country and show the corresponding customer.

at the moment, although i can do the count and sort decending,i cant seem to make the query shows only each top record from each country.. giving me 14 records.

when i try to 'Max' the name, it picks out the wrong customer.

Please help!

 

[Marat]

use something like:

SELECT Q.Country, Q.MaxSupplierID,  MAX(SELECT MAX(CompanyName) FROM Suppliers WHERE SupplierID=Q.MaxSupplierID)
FROM (SELECT Max(Suppliers.SupplierID) AS MaxSupplierID, Suppliers.Country
FROM Suppliers
GROUP BY Suppliers.Country) Q