Menu
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?
405
The CTE runs only once. The Nested Loops operator splits every row into four.
They can only be referenced within the scope of that particular command. As such your first select * statement would work, the next two would error out not being able to find the CTE. In order for those to work you would need create another CTE for each to reference.
A CTE provides better readability and also increases the performance as compared to the derived table. Unlike a derived table, a CTE is a subquery that can be self-referencing using its own name. It is also known as recursive CTE and can also be referenced multiple times in the same query.
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 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 ) 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
