>>>But we need to farther filter the result of cte. We just need to return the original number.
>>
>>You could use temp table (or table variable) to store CTE result and then get Totals from it and the final resultset also.
>>

>>DECLARE @Intermediate TABLE (field list, RowIndex int, TotalCount int)
>>INSERT INTO @Intermediate
>>SELECT * FROM Cte
>>
>>SELECT TOP 1 @Total = TotalCount FROM @Intermediate
>>SELECT * FROM @Intermediate
>>

>>something like this. Not tested.
>
>Hi Boris,
>
>I got a link to this article by e-mail today
>http://www.sqlservercentral.com/articles/T-SQL/66030/

I didn't see an example with TABLE VARIABLE and w/o Clustered index :-)
Kill me I don't know how to get the results that guy get. I see the actual execution plan but didn't see anything like his results.