Table based on values in CTE

Question

I have this SQL using a table-value constructor:

select * from (values ('a', 'b'), ('c', 'd')) v (e, f)

This works to select data from the table with alias v.

However, I want to build a with statement (CTE) such that it binds this value table with the alias v to use it in the select statement following the CTE.

Something like this:

with v as 
(
    values ('a','b'), ('c','d') t (e, f)
)
select e from v where f = 'd'

How is this possible?

Answer 1

You'll need to put the entire statement in the CTE:

with v as 
(
  select * from (values ('a','b'),('c','d')) v (e,f)
)
select e from v where f='d'

It may be helpful to think of a non-recursive CTE as syntactic sugar for a subquery. So this is the same logic as the subquery syntax:

select e
from (select * from (values ('a','b'),('c','d')) v (e,f)) v
where f='d'