Click here to Skip to main content
Click here to Skip to main content

Uncommon Common Table Expressions (CTEs)

By , 25 Aug 2009
Rate this:
Please Sign up or sign in to vote.

Common table expressions (CTEs) were introduced with SQL 2005. They are a powerful new construct for building advanced queries.

The Microsoft CTE Article describes 4 main uses for CTEs:

  • Recursive queries
  • Inline views
  • Grouping by derived columns
  • Reuse of the same table

It still amazes me how many people on the new SQL platform actually aren't familiar with or don't use CTEs. I'm going to share two quick-and-dirty examples to demonstrate just how powerful they can be.

Hierarchical Data

I've blogged a bit about hierarchical data in the past (read Hierarchical Data Templates in Silverlight for an example). SQL makes it far easier to retrieve data in a hierarchical fashion using CTEs.

Let's assume you have a content management site with articles, and articles can be grouped into hierarchical categories (i.e. one category may contain others). There are several ways to approach this, but one way is to have the main category table referenced by another that encapsulates the relationships, like this:

figure1.png

We'll start with an Articles section that has Whitepapers as a subcategory, which also has "Technology Related" as a subcategory, then add a FAQ which has "Using the Site" and "Logging Out" as subcategories. Our main table looks like this:

CategoryID  Category
1 Articles
2 FAQ
3 Whitepapers
4 Using the Site
5 Logging Out
7 Technology Related

To show the relationships, we'll enter records into CategoryLevel like this:

Parent  Child
1 3
2 4
2 5
3 7

One real-world challenge would be showing a breadcrumb when the user is viewing a technology article. Given the child node (7, Technology Related) how do we recursively traverse the hierarchy to show all of the parent nodes?

Using a common table expression, we would alias the "top" of our query (the selection of the current category) and then join that to recursively traverse the tree, like this:

with RecursiveCat(CategoryID,Category) 
 as (
  select
    CategoryID,
    Category
  from dbo.Categories
  where CategoryID = 7
  union all 
  select
    L.ParentCategoryID as CategoryID, 
    Categories.Category 
  from dbo.CategoryLevels L 
  join RecursiveCat c 
    on c.CategoryID = L.ChildCategoryID
  inner join dbo.Categories 
    on L.parentCategoryID = Categories.CategoryID
 )
 select * from RecursiveCat

The key here is to note the "union all" with the join. The top query "kicks it off" and selects the target node. The union describes a recursive template ... on the inside, where we reference the CategoryLevels table that describes the parent-child relationships, we join to the Common Table Expression (CTE) on the outside. This means for each record, it joins back to itself and "unrolls" until the full hierarchy is exposed.

Running the above query yields:

CategoryID  Category
7 Technology Related
3 Whitepapers
1 Articles

As it walks up the tree.

Ranked Data

The other most common use is for ranked data. Let's take the above example even further, and declare an articles table:

figure2.png

The articles simple belong to a category and have a date when they were published. I'm leaving out details like title, body, etc for now.

A very common way to feature articles on a content website is to show the most recent article published to a given category. Assuming you have dozens of articles, what is the easiest way to do that?

Of course, if you are pragmatic, you'll probably put a MostRecentArticleID column on the Categories table or even introduce a new table to hold that. However, if you want to have fun (and let me keep my excuse for giving an example) you can use a rank function (we're not yet to the Common Table Expression, so bear with me). For now, try to ignore my typo as ArticlePublishData should be ArticlePublishDate. The first query will give you an idea of how the RANK works:

SELECT CategoryID, ArticleID, ArticlePublishData,
     ROW_NUMBER() OVER (PARTITION BY CATEGORYID ORDER BY ArticlePublishData DESC) As "MyRank"
   FROM Articles 

When we run this, we get all of the articles, but if you notice, the "MyRank" column resets for each category. Within a category, it starts at the most date date with "1" and then increments. This is because we are partitioning, or telling the CTE to reset the ranking, by the categories, and then ordering within those categories by the publish date. In this example, both 1 and 2 are in the same category, then 3 is in a new category.

CategoryID ArticleID  ArticlePublishData  Rank
1 2 2008-12-13 00:00:00:00  1
1 1 2006-12-14 00:00:00:00  2
2 3 2009-01-02 00:00:00:00  1

Now, it's just a simple matter to add a filter using a Common Table Expression. We'll alias the ranking function to extract the row, and then filter on the row:

;with CTE(CategoryID, ArticleID, ArticlePublishData, Row) AS (
SELECT CategoryID, ArticleID, ArticlePublishData,
     ROW_NUMBER() OVER (PARTITION BY CATEGORYID ORDER BY ArticlePublishData DESC) As "MyRank"
   FROM Articles) 
SELECT * from CTE
where Row = 1

This will give us only the most recent article for each category.

Hopefully this has been a useful, quick, and easy introduction to Common Table Expressions. Just search online for the phrase and you'll find plenty of examples and tutorials to help you take your SQL skills to the next level.

Jeremy Likness

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Jeremy Likness
Architect Wintellect
United States United States
Jeremy Likness is a principal consultant at Wintellect. Jeremy, an experienced entrepreneur and technology executive, has successfully helped ship commercial enterprise software for 20 years. He specializes in catalyzing growth, developing ideas and creating value through delivering software in technical enterprises. His roles as business owner, technology executive and hands-on developer provided unique opportunities to directly impact the bottom line of multiple businesses by helping them grow and increase their organizational capacity while improving operational efficiency. He has worked with several initially small companies like Manhattan Associates and AirWatch before they grew large and experienced their transition from good to great while helping direct vision and strategy to embrace changing technology and markets. Jeremy is capable of quickly adapting to new paradigms and helps technology teams endure change by providing strong leadership, working with team members “in the trenches” and mentoring them in the soft skills that are key for engineers to bridge the gap between business and technology.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
Questionis CTE necessary? Pinmemberivanicin31-Aug-09 13:19 
AnswerRe: is CTE necessary? PinmemberJeremy Likness31-Aug-09 15:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 25 Aug 2009
Article Copyright 2009 by Jeremy Likness
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid