reuse subquery results in where clause (newbie)

How can I reuse the result of a subquery in the same query where-clause
without having to duplicate the subquery itself? Say I have a query like:
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
order by someCount
and I want to filter the rows by someCount: if I add a clause like:
where someCount > 5
I get a syntax error (invalid column name). How can I refer to the
calculated field someCount without repeating the subquery as a whole?
Thanks!Dan
select <column lists> from
(
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
) as Der
where someCount >5
order by someCount
"Dan" <fusid@.iol.it> wrote in message
news:epI8jZRDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> How can I reuse the result of a subquery in the same query where-clause
> without having to duplicate the subquery itself? Say I have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
> Thanks!
>|||Dan wrote:
> How can I reuse the result of a subquery in the same query
> where-clause without having to duplicate the subquery itself? Say I
> have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
>
Move the subquery to your FROM clause:
select a,b, someCount
from MyTable t inner join
(select d, count(c) someCount from MyOtherTable
where MyOtherTable GROUP BY d) q
ON t.a = q.d
order by someCount
You may find this enlightening:
http://groups-beta.google.com/group...09662
c8
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.