The Maximum Impact Solutions Blog

How Do I Calculate the Top N Sales by Group using SQL?

Declare @TopN as Int
Set @TopN =5   
                           
SELECT     Employee.[First Name] + ' ' + Employee.[Last Name] AS [Employee Name], a.[Order Amount], COUNT(*) AS Rank_No
FROM         Orders AS a INNER JOIN
                      Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount] INNER JOIN
                      Employee ON b.[Employee ID] = Employee.[Employee ID]
GROUP BY a.[Employee ID], a.[Order Amount],
Employee.[First Name] + ' ' + Employee.[Last Name]
HAVING      (COUNT(*) <= @TopN)
ORDER BY a.[Employee ID], Rank_No