How to create CTE which uses another CTE as the data to further limit?
Sure, just reference the CTE directly:
WITH Source As
(
SELECT * FROM AllData
),
Filtered AS
(
SELECT * FROM Source WHERE ID = 4
)
SELECT * FROM Filtered
You can chain 2 (or more) CTE's 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;
Or the same example, with more "spelled out" names/aliases:
with ObjectsWithA (MyObjectId , MyObjectName) as
(
select object_id as MyObjIdAlias , name as MyNameAlias
from sys.objects
where name like '%A%'
),
ObjectsWithALessThan100 as
(
select * from ObjectsWithA theOtherCte
where theOtherCte.MyObjectId < 100
)
select lessThan100Alias.MyObjectId , lessThan100Alias.MyObjectName
from ObjectsWithALessThan100 lessThan100Alias
order by lessThan100Alias.MyObjectName;
A CTE can 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 the references have to be sequential. No forward references.