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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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