May 28, 2003 at 12:23 pm
Is is possible to use a column name assigned in an AS clause in a WHERE clause?
Here is an example of what I'm trying to do.
This of course does not execute. Thanks.
select distinct id,
count(id) as id_counter
from x
where id_counter > 1
May 29, 2003 at 6:13 am
Sure thing. Just put the original query inside a derived table:
select * from
(
select id,
count(*) as id_counter
from x
group by id
) as derived_table
where id_counter > 1
order by id_counter desc
May 29, 2003 at 6:48 am
Hi,
This query..
select distinct id,
count(id) as id_counter
from x
where id_counter > 1
wouldnt work because you are using a aggregate in the select list - you need to use group by for it to work..
using jays example - without the derived table, here's how you do it using a having clause
select id,
count(id) as id_counter
from x
group by id
having count(id) > 1
May 29, 2003 at 7:02 am
quote:
Hi,This query..
select distinct id,
count(id) as id_counter
from x
where id_counter > 1
wouldnt work because you are using a aggregate in the select list - you need to use group by for it to work..
using jays example - without the derived table, here's how you do it using a having clause
select id,
count(id) as id_counter
from x
group by id
having count(id) > 1
GRN,
sdidomenico's question was how to reference an alias in the WHERE clause. One of the things wrong with his original query (besides, as you pointed out, the missing GROUP BY clause) was that he directly referenced an alias in a WHERE clause. For some reason, SQL Server can't handle this. You have to reference the aliased column indirectly through a derived table, as illustrated above...
One interesting point in all this, is if you do a compare of the execution plans of both of these queries, you will find that SQL Server evaluates the following 2 statements to the same query plan...:
select * from
(
select id, count(*) as id_counter
from x
group by id
) as derived_table
where id_counter > 1
select id, count(*)
from x
group by id
having count(*) > 1
Hmmm, SQL Server is smart enough to know the best optimized plan is the same for both queries, but not smart enough to resolve an alias in a WHERE clause...
🙂
Jay
May 30, 2003 at 9:00 am
Thanks to everyone for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply