Developing Matt

My Technical Journal

Common table expressions

leave a comment »

Instead of using temp tables use this

WITH myCTE (column1, column2, column3)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)

select * from myCTE

The only limitation that I know if is that you have to use the resultset immediately (since it is a function),

whereas a temp table is stored until the end of the procedure.

However, you can use multiple CTEs and separate them with a comma as illustrated by the following (found from here ):


WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS

(

   SELECT

      CategoryID,

      CategoryName,

      (SELECT COUNT(1) FROM Products p

       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts

   FROM Categories c

),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS

(

   SELECT

      ProductID,

      CategoryID,

      ProductName,

      UnitPrice

   FROM Products p

   WHERE UnitPrice > 10.0

)

SELECT c.CategoryName, c.NumberOfProducts,

      p.ProductName, p.UnitPrice

FROM ProductsOverTenDollars p

   INNER JOIN CategoryAndNumberOfProducts c ON

      p.CategoryID = c.CategoryID

ORDER BY ProductName


		
Advertisements

Written by matt

February 4, 2008 at 11:27 am

Posted in Sql Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: