Top X in each group

-- TOP 3 / TOP 10 / TOP n per group - QUICK SYNTAX

;WITH CTE

     AS (SELECT PSC.Name     AS SubCategory,

                P.Name       AS ProductName,

                ROW_NUMBER() 

                  OVER(PARTITION BY P.ProductSubcategoryID

                        ORDER BY P.ProductID) AS RowID

         FROM   Production.ProductSubcategory PSC

                LEFT JOIN Production.Product P -- or INNER JOIN

                  ON P.ProductSubcategoryID = PSC.ProductSubcategoryID)

SELECT Subcategory,

       ProductName

FROM   CTE

WHERE  RowID <= 3

ORDER BY Subcategory,ProductName;

-- OR

DECLARE @TopN tinyint = 3;

              WITH cteTopNSales

     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.Person c

                    ON soh.SalesPersonID = c.BusinessEntityID

         WHERE    soh.SalesPersonID IS NOT NULL

         GROUP BY FirstName + ' ' + LastName,

                  sod.ProductID,

                  p.ProductNumber,

                  p.Name)

SELECT   *  FROM     cteTopNSales cte

-- Display top 3 for each group 

WHERE SeqNo <= @TopN

-- SeqNo = 2 will find the second highest in each group

ORDER BY ProductID,

         SeqNo

GO

-- OR

;WITH CTE AS

(SELECT   TOP 1 WITH TIES PSC.Name AS SubCategory,

                          P.Name   AS ProductName

 FROM     Production.ProductSubcategory PSC

          INNER JOIN Production.Product P   -- alternate LEFT JOIN

            ON P.ProductSubcategoryID = PSC.ProductSubcategoryID

 ORDER BY ROW_NUMBER()

           OVER(PARTITION BY P.ProductSubcategoryID

           ORDER BY ProductID) / (10+1) )

 SELECT * FROM CTE

 ORDER BY Subcategory,

          ProductName;

-- OR

SELECT   Subcategory,

         ProductName

FROM     (SELECT PSC.Name AS SubCategory,

                 P1.Name  AS ProductName,

                 (SELECT COUNT(* )

                  FROM   Production.ProductSubcategory PSC

                         LEFT JOIN Production.Product P2

                           ON P2.ProductSubcategoryID = PSC.ProductSubcategoryID

                  WHERE  P2.ProductSubcategoryID = P1.ProductSubcategoryID

                         AND P2.ProductID <= P1.ProductID) AS RowID

          FROM   Production.ProductSubcategory PSC

                 LEFT JOIN Production.Product P1

                   ON P1.ProductSubcategoryID = PSC.ProductSubcategoryID) AS X

WHERE    RowID <= 10

ORDER BY Subcategory,

         ProductName;