How can I reuse a Common Table Expression

Question

I am using a Common Table Expression for paging:

with query as (   Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,          FirstName,          LastName   From   Users ) Select * from query where TableRowNum between 1 and 25 Order By TableRowNum ASC 

Immediately after making this query, I make make an almost identical query in order to retrieve the total number of items:

with query as (   Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,          FirstName,          LastName   From   Users ) Select Count(*) from query 

I have tried combining these together (ie: define the CTE, query the data and then query the Count, but when I do this, I get an error message "Invalid object name 'query'" in response the the second query (the Count).

Is there any way to combine these two queries into one, to save a round-trip to the DB?

Answer 1

If you do not require them in 2 different queries, you can try

;with query as (   Select Row_Number() over (Order By UserID ASC) as TableRowNum,          FirstName,          LastName   From   Users ), totalCount AS (     SELECT COUNT(1) Total FROM query ) Select  query.*,         Total from    query, totalCount  where   TableRowNum  between 1 and 25  Order By TableRowNum ASC 

If you do require 2 different queries, rather use a table var

DECLARE @User TABLE(         TableRowNum INT,         FirstName VARCHAR(50),         LastName VARCHAR(50) ) ;with query as (   Select Row_Number() over (Order By UserID ASC) as TableRowNum,          FirstName,          LastName   From   Users ) INSERT INTO @User SELECT  TableRowNum,         FirstName,         LastName FROM    query  SELECT  * FROM    @User where   TableRowNum  between 1 and 25  Order By TableRowNum ASC  SELECT COUNT(1) FROM @User 

Answer 2

You can do that like this :

with query as (    Select   COUNT (*) OVER (PARTITION BY 1) AS TableTotalRows,  Row_Number() over (Order By OrderNum ASC) as TableRowNum,      FirstName,      LastName   From   Users )