Working remotely as a Freelancer Shipping logs to Azure Log Analytics

Drop us a line...

Send Message

Simple Example of Microsoft SQL Common Table Expression or CTE

Common Table Expression or CTE specifies a temporary named result set which is derived from a query and can be used in the same execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This is quite useful if you need temporary results from a sub query within your main query. An example below uses Northwind 2011 database:

Let’s break down the SQL code above:

  1. CTE Duplicates is defined and run. It returns a list of OrderID, grouped by OrderID and Quantity, where count of orders in a group is bigger then 1:
  2. The main query uses the results from Duplicates query in the Where clause:
  3. The result of the whole script is:

You can also use multiple CTE expressions in a single SQL statment. Check the example below:

You can also read more about CTE on Microsoft website.

About Miha
I've always been fascinated by computers. I am happy that I can work in this creative field of software development, where I can solve problems. I am also an avid cyclist :) and I love to learn...a lot!
Related Posts
  • All
  • By Author
  • By Category
  • By Tag