SQLTeam.com | Weblogs | Forums

HAVING statement

#1

Hello!

I can't get around the 'having' statement, can you tell me please why the below does not work?

ID, Col1, Col2
1, A, C
1, B, D
2, E, F
3, G, H
etc

select top 10 *
from [Table] 
group by [ID]
having count(*)>2

I need to return all the rows of ID=1

Thanks!

#2

select * from Table where ID=1

select count(ID),Col1,Col2
from Table
group by Col1,Col2
Having count(ID)>1

#3

What do you mean - can't get around?

When using GROUP BY you need to specify the columns to group on - and the aggregrated columns. In your sample query you are using an asterisk - which will not work. It is also a bad practice to use the asterisk to return data to a client (it can be used in other constructs - but generally it should not be used in production code).

This request appears to be trying to identify 'duplicate' rows. If the goal is to remove 'duplicates' then we can use ROW_NUMBER() function in a CTE and delete. If the goal is to just display the data:

  With dups
    As (
Select yt.ID
  From dbo.YourTable           yt
 Group By
       yt.ID
Having count(*) > 1
       )
Select yt2.ID
     , yt2.COL1
     , yt2.COL2
  From dbo.YourTable           yt2
 Inner Join dups                 d On d.ID = yt2.ID;

If you don't like using a CTE for this - then move that query into a derived table.