How to Select top n Records for Each Category

Question

I'm trying to write a query that joins 2 tables and will give me the top 5 names and the amount of items they sold at a location between a certain date range i.e. 01-01-2016 through 12-31-2017.

From what I've been researching, This is what I came up with:

SELECT
    EmployeeName, 
    COUNT(ID) AS 'Number of Deals', 
    CompanyNumber
FROM
(
    SELECT
        EmployeeName, 
        DealID, 
        CompanyNumber,
        ROW_NUMBER() OVER (PARTITION BY CompanyNumber ORDER BY DealID) AS rn
    FROM Deal
    JOIN DealEmployee
        ON Deal.DealID. =DealEmployee.DealID AS T
    WHERE
        Deal.Status = 2 AND
        Date BETWEEN '2016-01-01' AND '2017-12-31' AND
        EmployeeName != '' AND T.rn <=5 

I am hoping to get the the below result:

I am quite the novice, and I know my syntax is incorrect. Any help given would be greatly appreciated

Answer 1

not tested, but I would try something like:

with
basedata as (    
select EmployeeName
     , CompanyNumber
     , COUNT(ID) as Number_of_Deals
  from Deal
  join DealEmployee
    on Deal.DealID = DealEmployee.DealID    
 where Deal.Status = 2
   and Date between '2016-01-01' and '2017-12-31'
   and EmployeeName !=''
 group by EmployeeName
        , CompanyNumber 
)
,
basedata_with_rank as (
select t.*
     , row_number() over (partition by CompanyNumber order by Number_of_Deals desc) rn
  from basedata
)
select *
  from basedata_with_rank
 where rn <= 5
 order by CompanyNumber
        , Number_of_Deals desc

Using CTEs makes queries usually more readable. By the way: I would avoid to give a column the name "date" (a reserved word) and I would always use qualified names for my columns. Perhaps the use of the analytic functions rank or dense rank would be more appropriate, but row_number should also work.