How to find top 5 for each group with OVER PARTITION BY?

How to find top 5 for each group with OVER PARTITION BY?
Execute the following Microsoft SQL Server Transact-SQL script in Management Studio Query Editor to find the top 5 by specific grouping.
The ROW_NUMBER function in association with OVER PARTITION BY creates a sequence number according to the ORDER BY clause. The main SELECT takes the first 5 in each subset for the final report.

– SQL Server over partition by – sql server over clause

– SQL row_number over partition – sql select top function

USE AdventureWorks;

– Let a CTE do the hard work – CTE: Common Table Expression

WITH cteTopSales

AS (SELECT ROW_NUMBER()

OVER(PARTITION BY sod.ProductID

ORDER BY SUM(sod.LineTotal) DESC) AS SeqNo,

FirstName + ‘ ‘ + LastName   AS [Name],

ProductName = p.Name, – SQL Server currency formatting

‘$’ + 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)

– Easy-going main query

SELECT *

FROM cteTopSales cte

WHERE SeqNo <= 5

ORDER BY ProductID,

SeqNo

GO

/* Partial results

SeqNo Name              ProductName             TotalBySalesPerson

1     Shelley Dyck      Sport-100 Helmet, Red   $10,859.64

2     Linda Ecoffey     Sport-100 Helmet, Red   $10,410.01

3     Gail Erickson     Sport-100 Helmet, Red   $9,890.47

4     Michael Emanuel   Sport-100 Helmet, Red   $6,504.17

5     Maciej Dusza      Sport-100 Helmet, Red   $6,324.73

1     Shelley Dyck      Sport-100 Helmet, Black $11,677.54

2     Linda Ecoffey     Sport-100 Helmet, Black $11,673.99

*/

————

– T-SQL TOP 5 sales of high volumne bike dealers

USE AdventureWorks2008;

SELECT CustomerID, SalesOrderID, Sale, RecordNo

FROM (SELECT CustomerID,

SalesOrderID,

TotalDue AS Sale,

SUM(TotalDue) OVER(PARTITION BY CustomerID) AS SalesTotal,

ROW_NUMBER() OVER

(PARTITION BY CustomerID ORDER BY TotalDue DESC) AS RecordNo

FROM Sales.SalesOrderHeader) AS soh

WHERE SalesTotal > 100000

and soh.RecordNo < 6

ORDER BY CustomerID, RecordNo

/*

CustomerID  SalesOrderID      Sale        RecordNo

29484       50756             49846.693   1

29484       48395             43214.9511  2

29484       47454             36330.7417  3

29484       49495             32078.747   4

29484       45579             5410.064    5

29485       71782             43962.7901  1

29485       53459             42123.1691  2

…..

*/

————

More details: http://www.sqlusa.com/bestpractices2008/overpartitionby/