How to create CTE which uses another CTE as the data to further limit?

Question

I have searched this question here but couldn't find it, please redirect me if we already have it on the site.

I'm looking for a way to create CTE which uses another CTE as the data to further limit. I have a CTE which creates a report for me , but I would like to narrow this report with another input using the existing CTE.

I hope my question is clear.

Answer 1

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;

Answer 2

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.