Using output from "with as" few times

I can't 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 any tip to use t more than on time?

---

You cannot do this. CTEs can only be used in one statement (though you can use it multiple times within one statement.

Take a look at this article. Snippit:

Once a CTE is defined, it can be referenced multiple times by the first query that follows it.

And from their documentation:

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, "scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

---

Is there any tip to use t more than on time?

Sure, if you are doing this in a stored proc, just dump it into a #temp table. At the end of the stored proc, 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 you attempt to create it again, otherwise the next select ..into #name will error out with #name already exists

---

You can use a CTE only in a single query that follows the CTE. You can however make the query that follows as complicated as you wish.

In your example, you might consider a union between the 2 selects depending on precisely what you want. The code you gave isn't descriptive enough for me to suggest any other variants.