| How to find top 10 for each group? |
|
Execute the following
script in Query Editor to find the top 10 salesperson for each product(group). The ROW_NUMBER function creates a sequence number in descending sales order for each product in the CTE. The main SELECT takes the first 10 in each subset for the final report:
USE AdventureWorks;
WITH cteTop10Sales
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY sod.ProductID ORDER BY SUM(sod.LineTotal) DESC) As SeqNo
, FirstName +' '+LastName AS [Name]
, ProductName = p.Name
, '$'+convert(varchar,convert(money,SUM(sod.LineTotal)),1) AS TotalBySalesPerson
, p.ProductNumber
, sod.ProductID
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Person.Contact c
ON soh.SalesPersonID = c.ContactID
WHERE soh.SalesPersonID IS NOT NULL
GROUP BY FirstName +' '+LastName, sod.ProductID, p.ProductNumber, p.Name)
SELECT *
FROM cteTop10Sales cte
WHERE SeqNo <= 10
ORDER BY ProductID, SeqNo
GO
|
| SQLUSA - The Best SQL Server
2005 Training in the World |
|