How to create a CTE that uses another CTE as data for further limitation?

Question

I searched for this question here, but could not find it, please redirect me if we already have it on the site.

I am looking for a way to create a CTE that uses other CTEs as data for further limitation. I have a CTE that generates a report for me, but I would like to narrow this report down with another input using the existing CTE.

Hope my question is clear.

Answer 1

You can combine 2 (or more) CTEs together.

for example

with ObjectsWithA as ( select * from sys.objects where name like '%A%' ), ObjectsWithALessThan100 as ( select * from ObjectsWithA where object_id < 100 ) select * from ObjectsWithALessThan100; 

Answer 2

CTE may refer to previous CTEs:

 with report as ( <your query here> ), reportLimited as ( select * from report where foo = @bar ) select * from reportLimited 

The only rule is that links must be consistent. No direct links.

Answer 3

Of course, just go directly to the CTE:

 WITH Source As ( SELECT * FROM AllData ), Filtered AS ( SELECT * FROM Source WHERE ID = 4 ) SELECT * FROM Filtered 

Answer 4

 WITH Source ---------1--------- As ( SELECT * FROM emp ), destination----2---------- AS ( SELECT * FROM Source WHERE E_id = 4 ) SELECT * FROM destination