SQLUSA

Microsoft SQL Server 2008 Best Practices

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

.....

*/

------------

 

 

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page