Working remotely as a Freelancer Shipping logs to Azure Log Analytics

    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