Common Table Expression?

[N. Shamsundar]

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston

[Simon Hayes]

"N. Shamsundar" <sh******************@nospam.xyz> wrote in message
news:c4***********@masala.cc.uh.edu...

What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston

If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon

[Sjaak van Esdonk]

something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year