How to find TOP 3 in each group?

最新推荐文章于 2023-02-18 00:30:17 发布

yuxuac

最新推荐文章于 2023-02-18 00:30:17 发布

阅读量702

点赞数

分类专栏： SQL 文章标签： TSQL

SQL 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

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

    TotBySP = TotalBySalesPerson
    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;

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

http://www.sqlusa.com/bestpractices2008/top-group-by/