SQL:Using TOP in conjunction with GROUP BY

[BenFinkel]

Hey Guys,

I've got a question about some SQL I'm trying to write. What I've got is a table of Contacts, each of whom has a Sales figure associated with them, along with a territory.

I'd like an SQL statement to select the the top 5 sales figures for each territory.

In other words, I'd like to see the following result:

GEORGIA:Joe:5000
GEORGIA:Sam:4000
GEORGIA:Alex:3800
GEORGIA:Tim:3700
GEORGIA:Beth:3200
NEW YORK:Mabel:5100
NEW YORK:Terry:4850
NEW YORK:Jimbo:4100
NEW YORK:Leann:3600
NEW YORK:Mary:3500
IOWA:Jerry:4610
IOWA:Frank:4500
IOWA:Sally:3900
IOWA:Nancy:3650
IOWA:Greg:3110
...

From a table that is structured exactly the same, but has 150 contacts in each territory.

Any ideas?

Thanks!

[Ecniv]

SELECT qryTerSales.SumOfSales, qryTerSales.TerritoryID, qryTerSales.TerNameID, tblTerritories.Territory, tblTerName.TerName

FROM (
(SELECT top 5 tblTerSales.TerritoryID, tblTerSales.TerNameID, Sum(tblTerSales.Sales) AS SumOfSales
FROM tblTerSales
GROUP BY tblTerSales.TerritoryID, tblTerSales.TerNameID
order by Sum(tblTerSales.Sales)
) AS qryTerSales 
LEFT JOIN tblTerName ON qryTerSales.TerNameID = tblTerName.TerNameID) 
LEFT JOIN tblTerritories ON qryTerSales.TerritoryID = tblTerritories.TerritoryID

GROUP BY qryTerSales.SumOfSales, qryTerSales.TerritoryID, qryTerSales.TerNameID, tblTerritories.Territory, tblTerName.TerName
ORDER BY tblTerritories.Territory;

Something like that (made up of relative tables...


Vince