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