Menu
I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables.
My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.
My solution to this is to add a CTE that groups scheduled events and counts the number of participants.
This will allow me to join in that information per scheduled event. Keeping the query simple.
I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?
I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?
I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.
481
After you've defined the first CTE, it is separated from the second one only by the comma, i.e. you write WITH only once. After that, it doesn't matter how many CTEs you define; it's only important that you separate them by comma and start every CTE using its name.
A CTE is similar to a derived table in that it is not stored and lasts only for the duration of the query. Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain.
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
You can have multiple CTEs in one query, as well as reuse a CTE:
WITH cte1 AS ( SELECT 1 AS id ), cte2 AS ( SELECT 2 AS id ) SELECT * FROM cte1 UNION ALL SELECT * FROM cte2 UNION ALL SELECT * FROM cte1 Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
