Using output “c” as “several times”

Question

I can not use t from

with t as ( select row_number() OVER (partition by ID ORDER BY id) as numb, * from my_table where id= 6 ) select top 2 from t # it works select top 2 from t # here I get error Invalid object name 't'. 

Is there a tip to use t more than in time?

Answer 1

You cannot do this. CTEs can only be used in one expression (although you can use it several times in one expression.

Take a look at this article . Snippit:

After determining the CTE, it can be repeatedly referenced in the first request that follows it.

And from their documentation :

A common table expression (CTE) can be a temporary result set that is determined during the execution of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Emphasis on "the scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

Answer 2

Is there a tip to use t more than in time?

Of course, if you do this in a stored procedure, just upload it to the #temp table. At the end of the stored procedure, the #temp table disappears.

 with t as ( select row_number() OVER (partition by ID ORDER BY id) as numb, * from my_table where id= 6 ) select * into #tmp from t select top 2 from #tmp -- good select top 2 from #tmp -- good also 

Outside of SP, just make sure you drop the # table before trying to create it again, otherwise the next select ..into #name will fail with the error #name already exists

Answer 3

You can use CTE in only one request that follows the CTE. However, you can make the request as complex as you wish.

In your example, you might consider combining between two choices depending on what exactly you want. The code you gave is not descriptive enough to suggest any other options.