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;

...

Below is 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;