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