|
Researching Employees with CTE Constructs
By Kalman Toth, M.Phil., M.Phil., MCDBA
June 25, 2005
The CTE (Common Table Expression) construct in SQL Server 2005
Transact-SQL makes very powerful yet simple SELECT statements
feasible. The following example will return a result set with
ordered selection of 21and 40 rows:
USE AdventureWorks
WITH EmployeeCTE AS
(SELECT FirstName, LastName, e.Title, EmailAddress,
ROW_NUMBER() OVER (ORDER BY c.ContactID) AS RowNumber
FROM Person.Contact c
JOIN HumanResources.Employee e
ON c.ContactID=e.ContactID
)
SELECT * FROM EmployeeCTE
WHERE RowNumber BETWEEN 21 AND 40
ORDER BY LastName, FirstName
GO
Here is the result set:
FirstName LastName Title Email RowNumber
Wanida Benshoof Marketing Assistant wanida0@adventure-works.com
29
Michael Blythe Sales Representative michael9@adventure-works.com
26
David Bradley Marketing Manager david0@adventure-works.com 28
Kevin Brown Marketing Assistant kevin0@adventure-works.com 30
Mary Dempsey Marketing Assistant mary2@adventure-works.com 31
Terry Eminhizer Marketing Specialist terry0@adventure-works.com
32
Mary Gibson Marketing Specialist mary0@adventure-works.com 34
Sariya Harnpadoungsataya Marketing Specialist sariya0@adventure-works.com
33
Gordon Hee Buyer gordon0@adventure-works.com 40
Annette Hill Purchasing Assistant annette0@adventure-works.com
38
Reinout Hillmann Purchasing Assistant reinout0@adventure-works.com
39
Tete Mensa-Annan Sales Representative tete0@adventure-works.com
27
Jae Pak Sales Representative jae0@adventure-works.com 25
Lynn Tsoflias Sales Representative lynn0@adventure-works.com 24
Rachel Valdez Sales Representative rachel0@adventure-works.com
23
Garrett Vargas Sales Representative garrett1@adventure-works.com
21
Ranjit Varkey Chudukatil Sales Representative ranjit0@adventure-works.com
22
Jill Williams Marketing Specialist jill0@adventure-works.com 35
John Wood Marketing Specialist john5@adventure-works.com 36
Sheela Word Purchasing Manager sheela0@adventure-works.com 37
|
