|
Return to Newsletter Contents...
WITH and Ranking Functions
by:
Charles Tournear, Senior Consultant,
MCT, MCSE, MCSD, MCDBA, CRCP
The WITH clause, in the
Transact-SQL language, specifies a temporary named query result set, known as a
common table expression (CTE). This is derived from a query and can be used
within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.
This clause can also be used in a CREATE VIEW statement as part of its defining
SELECT statement. A common table expression can also be recursive by including
references to itself in the CTE definition.
The CTE begins with the name of the CTE followed by a column
list. The column list must include
the same number of column names as the output of the query result set.
Here is a very simple example of using a CTE (The gray blocked text
defines the CTE):
WITH
ManagerEmployeeList(Manager,
Employee, EmployeeID)
AS
(SELECT
isnull(mc.FirstName,'')
+ rtrim('
' + isnull(mc.MiddleName,''))
+ ' '
+ isnull(mc.LastName,'')
as ManagerName,
isnull(c.FirstName,'')
+ rtrim('
' + isnull(c.MiddleName,''))
+ ' '
+ isnull(c.LastName,'')
as EmployeeNamne,
e.EmployeeID
FROM
Person.Contact c INNER
JOIN HumanResources.Employee
e ON c.ContactID
= e.ContactID
INNER
JOIN Person.Contact
mc ON mc.ContactID
= e.ManagerID)
SELECT Manager,
Count(EmployeeID)
AS NoEmployees FROM
ManagerEmployeeList
GROUP BY
Manager
The output from the above would be:
Manager
NoEmployees
-------------------------------------------------- -----------
Adrian Dumitrascu
3
Alexander J. Berger
6
Andreas Berglund
4
Andrew Cencini
7
Ann Beebe
8
Anna A. Albright
8
Bart Duncan
11
Blaine Dockter
2
Brigid F. Cavendish
13
Chris Ashton
7
. . .
The general rules for defining a CTE are:
-
CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement
-
With clauses cannot be nested or used in sub-queries
-
CTE cannot contain: COMPUTE, ORDER BY (except with TOP keyword), INTO, query
hints, FOR XML, or FOR BROWSE
-
Can be used for defining a server-side cursor
For more information about restrictions on recursive CTE’s and examples of
recursion look up “WITH common_table_expression (Transact-SQL)” in SQL
Server 2005 Books online.
The WITH clause is often used with the Transact-SQL ranking functions.
Ranking functions return a ranking value for each row in a partition.
Depending on the function used, some rows might receive the same value as other
rows. The Ranking functions include:
RANK, NTILE, DENSE RANK, and ROW_NUMBER.
Ranking functions can use the OVER clause to control how the ranking will
occur.
The RANK function returns a ranking value for each row in a result set.
The rank is one plus the number of rows that came before the current row.
Example:
WITH
ProductQuantityByLocation(ProductID,
ProductName, Location,
Quantity, Ranking)
AS
(SELECT
i.ProductID, p.Name,
l.Name,
i.Quantity,
RANK() OVER
(PARTITION
BY i.LocationID
order by i.Quantity)
as RANK
FROM
Production.ProductInventory i
JOIN Production.Product
p
ON
i.ProductID = p.ProductID
JOIN
Production.Location l
ON l.LocationID
= i.LocationID)
SELECT ProductName,
Location, Ranking
FROM ProductQuantityByLocation
ORDER BY PRODUCTNAME,
Ranking DESC
The output for the above would be:
ProductName
Location
Ranking
------------------------------ ------------------------------
--------------------
Adjustable Race
Subassembly
117
Adjustable Race
Tool Crib
78
Adjustable Race
Miscellaneous Storage
71
All-Purpose Bike Stand
Finished Goods Storage
115
AWC Logo Cap
Finished Goods Storage
139
BB Ball Bearing
Miscellaneous Storage
115
BB Ball Bearing
Tool Crib
110
BB Ball Bearing
Subassembly
106
Bearing Ball
Subassembly
122
Bearing Ball Tool
Crib
85
Bearing Ball
Miscellaneous Storage
67
Bike
Wash
- Dissolver
Finished Goods Storage
14
. . .
In the above example, the RANK function is followed by the OVER clause where the
Partition By defines the grouping of the items and order by defines the Sort
order for creating the ranking values.
The ROW_NUMBER function returns a sequential number of a row within a partition
of a result set. This can be
especially useful in server side cursors when you wish to access a specific rows
by their row number. The following
example returns rows 51 through 60 inclusive.
WITH OrderedOrders
AS
(SELECT
SalesOrderID, OrderDate,
ROW_NUMBER() OVER
(order
by OrderDate)as
RowNumber
FROM
Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber between 51
and 60
The output from the above would be:
SalesOrderID OrderDate
RowNumber
------------ ----------------------- --------------------
43709
2001-07-03 00:00:00.000 51
43710
2001-07-03 00:00:00.000 52
43711
2001-07-04 00:00:00.000 53
43712
2001-07-04 00:00:00.000 54
43713
2001-07-05 00:00:00.000 55
43714
2001-07-05 00:00:00.000 56
43715
2001-07-05 00:00:00.000 57
43716
2001-07-05 00:00:00.000 58
43717
2001-07-05 00:00:00.000 59
43718
2001-07-06 00:00:00.000 60
In the above example the OVER clause defines the sort order for defining the row
numbers.
For more information about the ranking functions lookup “Ranking Functions
(Transact-SQL)” in the SQL Server 2005 Book Online.
When building complex queries from other queries, you should consider using the
WITH clause and a
common table expression to access the data in the way that you want it instead
of creating a view of a view. Also
the ranking functions can be used to provide some selection or grouping concepts
without having to rely on the Group By clause.
Go to Top |
Return to Newsletter Contents
|
