How to reference one CTE twice?
Solution 1
You can use commas to create multiple CTEs that references the CTEs Above.
Just to illustrate what I mean:
with recs as (
select
*,
row_number() over (order by id) as rownum from ......
),
counts as (
select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts
where rownum between @a and @b ....
This is not the a good solution.
The best solution I found to have the total count in a CTE without counting the records is described in this article.
DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq
Solution 2
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.
Solution 3
You could append a field that has the total rows in it, of course it will be on every row
select recs.*,totalrows = (select count(0) from recs)
from recs
Solution 4
This is the best:
;WITH recs AS
(SELECT a,b,c,
row_number() over (
ORDER BY id) AS RowNum,
row_number() over () AS RecordCount
FROM ......)
SELECT a,b,c,rownum,RecordCount FROM recs
WHERE rownum BETWEEN @a AND @b
Solution 5
This is how we deal with paging (without session management for now) in a production environment. Performs as expected.
DECLARE
@p_PageNumberRequested int = 1,
-- Provide -1 to retreive all pages with all the rows.
@p_RowsPerPage int = 25
;WITH Numbered AS (
SELECT
ROW_NUMBER() OVER (ORDER BY YourOrdering) AbsoluteRowNumber
, COUNT(1) OVER () TotalRows
, YourColumns
FROM
YourTable
),
Paged AS (
SELECT
(AbsoluteRowNumber - 1) / @p_RowsPerPage + 1 PageNumber,
*
FROM
Numbered)
SELECT
ROW_NUMBER() OVER(PARTITION BY PageNumber ORDER BY AbsoluteRowNumber) RowNumberOnPage,
*
FROM
Paged
WHERE
PageNumber = @p_PageNumberRequested
OR
@p_PageNumberRequested = -1
ORDER BY
AbsoluteRowNumber
Related videos on Youtube
Nathan Ridley
Need a mentor? https://www.codementor.io/nathanridley I'm an independent developer from Brisbane, Australia. My interests and skillset in development cross the board, from UI and graphic design to HTML/CSS and back end development, including database design and development. I am also learning game and graphics development, formerly with C# and SharpDX and lately with C++ and OpenGL. The primary technologies I use for business/web application development are C#, .Net, ASP.Net MVC, Web API, HTML, CSS, JavaScript, SQL Server, MongoDB, Redis, node.js and anything useful that is related. GitHub Projects (coding) Dribbble Profile (design work)
Updated on January 24, 2020Comments
-
Nathan Ridley over 4 years
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?
-
Mike Cole about 12 yearsI would perhaps consider renaming this question since the accepted answer doesn't actually use the CTE twice.
-
-
Abe Miessler over 14 yearsAlso, I'd recommend using those "SELECT *"s only if you truly need them. They can cause performance issues, and most of the time aren't really necessary.
-
David Hall over 14 yearsThis syntax for creating the temp table may also prove useful: Select * Into #Recs From...
-
Nathan Ridley over 14 yearsActually I have a complex SELECT statement I need to do on hierarchical data and the way in which it is called will vary heavily depending on the situation.
-
Nathan Ridley over 14 yearsYeah I thought of this, but there is an issue when the query returns no records. I guess I could fudge it with a UNION ALL and a dummy row...
-
Abe Miessler over 14 yearsHmmm, are you saying that the structure of the CTE/Temp table will vary? If that is the case then I would recommend David Hall's suggestion. That will allow you to define the structure of your temporary table based on what you select (similar to your CTE).
-
Nathan Ridley over 14 yearsMy issue with using a temp table is that I don't want to stuff half a million or more rows into a table. Seems inefficient to do it that way.
-
Jose Chama over 14 yearsCheck out the last piece of code I took from the article. What is does it has an ascending and descending row count and it just add them in the results to get the total number of rows. This performs really good in our production environments.
-
Nathan Ridley over 14 yearsAhh brilliant! That link has a really good way to achieve this.
-
Edyn over 11 yearsThis solution can be quite slow on large datasets... the COUNT option listed below by jw56578 should work just fine, and it's a lot cleaner.
-
jpgrassi over 9 yearsThis works on a simple CTE query but how about a Parent/Child recursive CTE? Tried here and it didn't work (or I missed something)
-
tribe84 over 8 yearsHow about using COUNT(1) OVER () AS TotalRows?