The code below produces my desired result of using the Row_Number () function to Rank a number of agents within their Sales Territory (Partitioned by Terr) by each Line of business (2nd Partition Line) using Items Sold to Order them from high to low (best to worse within each line). There are 5 Territories and 5 Liness of business.
The query works as there are about 1200 agents with 5 lines of business and my result contains 6000 rows all in descending order by Terrr. I am happy. However, I am being ask to supply only the Top 20 for each terr by line of business. So I tried to a Select Top 20 and that returns the Top 20 rows in the query.
Any suggestions, perhaps a stored procedure?
SELECT TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS Rank, Terr, Mkt, AgtNo, Items, Line