With q as query

Its cool to use subqueries, but it can be even cooler if you want to take data from a result and handle that in different way.

And can be used instead of creating a temp table.

;with q as (
select a,b,c from table a
)

select b from q where c = 'something'
select a from q where b = 'somethingelse'