DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find top 10 for each group?

Execute the following SQL Servr T-SQL script in Query Editor to find the top 10 salesperson for each product(group) sold by AdventureWorks Cycles.

The ROW_NUMBER function creates a sequence number in descending sales order for each product in the CTE. The main SELECT query filters the first 10 in each subset for the final report:

USE AdventureWorks;

 

-- SQL group by top n - SQL row_number - SQL over partition by - SQL cte

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

-- Display top 10 for each group in sql server

WHERE    SeqNo <= 10

ORDER BY ProductID,

         SeqNo

GO

 

/* Partial results

 

SeqNo Name              ProductName                   TotalBySalesPerson

1     Linda Ecoffey     ML Road Frame-W - Yellow, 38  $53,080.46

2     Gail Erickson     ML Road Frame-W - Yellow, 38  $49,219.48

3     Maciej Dusza      ML Road Frame-W - Yellow, 38  $31,212.35

4     Jauna Elson       ML Road Frame-W - Yellow, 38  $30,887.90

5     Shelley Dyck      ML Road Frame-W - Yellow, 38  $29,427.86

6     Carol Elliott     ML Road Frame-W - Yellow, 38  $25,437.09

7     Carla Eldridge    ML Road Frame-W - Yellow, 38  $23,814.83

8     Mark Erickson     ML Road Frame-W - Yellow, 38  $22,062.78

9     Michael Emanuel   ML Road Frame-W - Yellow, 38  $11,096.28

10    Martha Espinoza   ML Road Frame-W - Yellow, 38  $9,052.23

*/

Related articles:

More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions

Who's On First? Solving the Top per Group Problem (Part 1: Technique)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE