Menu
I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set.
with recs as (select *, row_number() over (order by id) as rownum from ......) select * from recs where rownum between @a and @b .... select count(*) from recs Obviously my query above is patchy, but it's just for illustrating my point. I want a page of results AND the total number of matches. How do I do this without having to literally copy and paste the entire 20+ line CTE?
201
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.
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.
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.
CTE benefits CTEs can reference the results multiple times throughout the query. By storing the results of the subquery, you can reuse them throughout a larger query.
Don't think you can. From MSDN
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
Emphasis on "single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."
This might be a situation where you want to use a Temporary Table.
CREATE TABLE #Recs { ..... } INSERT INTO #Recs select *, row_number() over (order by id) as rownum from ...... If you don't know the structure of the table before hand you can use this form to create a temporary table:
select *, row_number() over (order by id) as rownum INTO #Recs from ...... You will be able to use the Temporary table in the manner you have described above.
126
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
