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 3 in each group?

Execute the following Microsoft SQL Server T-SQL example scripts in SSMS Query Editor to demonstrate the TOP function usage in TOP n per group and other queries.

The FORMAT and CONCAT functions are new in SQL Server 2012.

USE AdventureWorks2008;

 

-- 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;

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

 

------------
-- TOP is indirectly impliemented by the use of GROUP BY within a CTE
-- to create summary report TOP n query.
------------
-- FIND TOP 3 in each group - SQL top group by
- SQL group by top n

-- SQL top - SQL over partition by - SQL cte - Common Table Expression

DECLARE @TopN tinyint = 3;

WITH cteTopNSales

     AS (SELECT   Row_number()

                    OVER(PARTITION BY sod.ProductID

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

                  CONCAT(FirstName, ' ', LastName)    AS [Name],

                  ProductName = p.Name,

                  FORMAT(Convert(MONEY,Sum(sod.LineTotal)),

                       'c','en-US') 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

/* Partial results

  1. TotBySP = TotalBySalesPerson
  2. ProdNo = ProductNumber 

SeqNo Name              ProductName             TotBySP     ProdNo      ProductID

1     Linda Mitchell    Sport-100 Helmet, Red   $10,859.64  HL-U509-R         707

2     Jillian Carson    Sport-100 Helmet, Red   $10,410.01  HL-U509-R         707

3     Jae Pak           Sport-100 Helmet, Red   $9,890.47   HL-U509-R         707

1     Linda Mitchell    Sport-100 Helmet, Black $11,677.54  HL-U509           708

2     Jillian Carson    Sport-100 Helmet, Black $11,673.99  HL-U509           708

3     Jae Pak           Sport-100 Helmet, Black $10,129.46  HL-U509           708

1     Tsvi Reiter       Mountain Bike Socks, M  $1,141.80   SO-B909-M         709

2     Linda Mitchell    Mountain Bike Socks, M  $875.21     SO-B909-M         709

3     Jillian Carson    Mountain Bike Socks, M  $826.79     SO-B909-M         709

*/

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

 

-- SELECT TOP 10 per group - CTE ORDER BY ROW_NUMBER PARTITION BY - SS 2005 and on

;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;

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

 

------------
-- The following script shows a direct use of the SQL Server TOP function.
------------
-- Create an empty table with TOP 0
- SQL TOP function

SELECT TOP (0) * INTO #ProductX

FROM Production.Product

GO

-- (0 row(s) affected)

SELECT COUNT(*) FROM #ProductX

GO

-- 0

DROP TABLE #ProductX

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

 

 

-- TOP 10 per group - LEFT JOIN subquery  - All versions of SQL Server

SELECT   PSC.Name AS SubCategory,

         P.Name   AS ProductName

FROM     Production.ProductSubcategory PSC

         LEFT JOIN Production.Product P

           ON P.ProductSubcategoryID = PSC.ProductSubcategoryID

WHERE    P.ProductID IN (SELECT   TOP 10 ProductID

                         FROM     Production.Product P

                         WHERE    P.ProductSubcategoryID = PSC.ProductSubcategoryID

                         ORDER BY P.ProductID DESC)

ORDER BY Subcategory,

         ProductName;

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

 

-- TOP 10 per group SELF JOIN subquery  - All versions of SQL Server

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;

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

 

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