|
Researching Purchase Orders with TOP ExpressionS
By Kalman Toth, M.Phil., M.Phil., MCDBA
June 25, 2005
The TOP clause has a parametric version in SQL Server 2005 Transact-SQL.
Instead of a hard-wired value we can pass along a variable to
indicate the limitation on the output. The following example will
return two result sets with random selection of 7 and 15 rows:
USE AdventureWorks
DECLARE @TopList AS INT
SET @TopList = 7
SELECT TOP(@TopList)
PurchaseOrderID AS PO,
ProductName=p.Name, DueDate=convert(char,DueDate,101),
UnitPrice=convert(decimal(10,2),UnitPrice), OrderQty
From Purchasing.PurchaseOrderDetail pod
JOIN Production.Product p
ON pod.ProductID=p.ProductID
WHERE UnitPrice between 30.00 and 40.00
SET @TopList = 15
SELECT TOP(@TopList)
PurchaseOrderID AS PO,
ProductName=p.Name, DueDate=convert(char,DueDate,101),
UnitPrice=convert(decimal(10,2),UnitPrice), OrderQty
From Purchasing.PurchaseOrderDetail pod
JOIN Production.Product p
ON pod.ProductID=p.ProductID
WHERE UnitPrice between 30.00 and 40.00
GO
Here is the first result set:
PO Product DueDate UnitPrice OrderQty
5 HL Road Rim 06/14/2001 37.09 550
7 ML Crankarm 06/14/2001 33.58 550
11 Lock Nut 17 01/28/2002 39.12 3
15 Pinch Bolt 01/28/2002 34.19 3
19 ML Mountain Seat/Saddle 01/29/2002 30.44 550
19 ML Road Seat/Saddle 01/29/2002 30.44 550
27 Flat Washer 6 01/29/2002 39.07 3
To get an ordered set we have to add the ORDER BY clause on a
column. The following query will yield the top 7 order quantity:
DECLARE @TopList AS INT
SET @TopList = 7
SELECT TOP(@TopList)
PurchaseOrderID AS PO,
ProductName=p.Name, DueDate=convert(char,DueDate,101),
UnitPrice=convert(decimal(10,2),UnitPrice), OrderQty
From Purchasing.PurchaseOrderDetail pod
JOIN Production.Product p
ON pod.ProductID=p.ProductID
WHERE UnitPrice between 30.00 and 40.00
ORDER BY OrderQty
Here is the first result set:
PO Product DueDate UnitPrice OrderQty
11 Lock Nut 17 01/28/2002 39.12 3
15 Pinch Bolt 01/28/2002 34.19 3
27 Flat Washer 6 01/29/2002 39.07 3
27 Flat Washer 9 01/29/2002 39.07 3
27 Flat Washer 4 01/29/2002 36.97 3
29 Hex Nut 6 02/22/2002 39.07 3
29 Hex Nut 16 02/22/2002 36.97 3
If we add the WITH TIES option, the output increases to 867 rows.
Here is the query:
DECLARE @TopList AS INT
SET @TopList = 7
SELECT TOP(@TopList) WITH TIES
PurchaseOrderID AS PO,
ProductName=p.Name, DueDate=convert(char,DueDate,101),
UnitPrice=convert(decimal(10,2),UnitPrice), OrderQty
From Purchasing.PurchaseOrderDetail pod
JOIN Production.Product p
ON pod.ProductID=p.ProductID
WHERE UnitPrice between 30.00 and 40.00
ORDER BY OrderQty
|
