|
Execute the following
SQL Server T-SQL example scripts in SSMS Query Editor to demonstrate the TOP function.
First, TOP is indirectly impliemented by the use of GROUP BY within a CTE to create summary report TOP n query.
The second script shows a direct use of the SQL Server TOP function.
USE AdventureWorks2008;
-- 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
*/
------------
-- 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
GO
|
