how to retrieve only rows where count of ID > 1?

Hello,
Select ID, Count(ID) From tbl1
Group By ID Having Count(ID) > 1
this retrieves only IDs where there are duplicate IDs.
Select ID, fldx from tbl1 Group By ID, fldx Having Count(ID) > 1
this is the same table, but does not return any rows. How can I retrieve
only the rows (the entire row) Having Count(ID) > 1?
Thanks,
RichDDL, sample data, desired results?
http://www.aspfaq.com/5006
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:832BFBC9-FEF0-4084-8FD1-453D3B7302EE@.microsoft.com...
> Hello,
> Select ID, Count(ID) From tbl1
> Group By ID Having Count(ID) > 1
> this retrieves only IDs where there are duplicate IDs.
> Select ID, fldx from tbl1 Group By ID, fldx Having Count(ID) > 1
> this is the same table, but does not return any rows. How can I retrieve
> only the rows (the entire row) Having Count(ID) > 1?
> Thanks,
> Rich|||I came up with this:
Select * from tbl1
Where ID In (Select ID From tbl1
Group By ID Having Count(ID) > 1)
I think a self join might be more efficient. Any suggestions appreciated on
how I could do the self join to get the same result.
"Rich" wrote:

> Hello,
> Select ID, Count(ID) From tbl1
> Group By ID Having Count(ID) > 1
> this retrieves only IDs where there are duplicate IDs.
> Select ID, fldx from tbl1 Group By ID, fldx Having Count(ID) > 1
> this is the same table, but does not return any rows. How can I retrieve
> only the rows (the entire row) Having Count(ID) > 1?
> Thanks,
> Rich|||One possibility (untested):
Select y.ID, ... From tbl1 y
WHERE (SELECT COUNT(*) FROM tbl1 x WHERE x.ID = y.ID) > 1
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:832BFBC9-FEF0-4084-8FD1-453D3B7302EE@.microsoft.com...
> Hello,
> Select ID, Count(ID) From tbl1
> Group By ID Having Count(ID) > 1
> this retrieves only IDs where there are duplicate IDs.
> Select ID, fldx from tbl1 Group By ID, fldx Having Count(ID) > 1
> this is the same table, but does not return any rows. How can I retrieve
> only the rows (the entire row) Having Count(ID) > 1?
> Thanks,
> Rich|||Better yet, just shoot Aaron an email with a recent backup of the database
attached. ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eUYjtgZMGHA.1488@.TK2MSFTNGP14.phx.gbl...
> DDL, sample data, desired results?
> http://www.aspfaq.com/5006
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:832BFBC9-FEF0-4084-8FD1-453D3B7302EE@.microsoft.com...
>|||And here is the self join I had in mind which appears to work:
select t1.* from tbl1 t1 join
(select ID from tbl1 group by ID having count(ID) > 1) t2
on t1.ID = t2.ID
This is returning the recordset that I was looking for.
"Rich" wrote:
> I came up with this:
> Select * from tbl1
> Where ID In (Select ID From tbl1
> Group By ID Having Count(ID) > 1)
> I think a self join might be more efficient. Any suggestions appreciated
on
> how I could do the self join to get the same result.
>
> "Rich" wrote:
>|||"JT" <someone@.microsoft.com> wrote in message
news:%23FllinZMGHA.2916@.tk2msftngp13.phx.gbl...
> Better yet, just shoot Aaron an email with a recent backup of the database
> attached. ;-)
Ouch... Are you looking for a fight JT?|||This solution is interesting, but it did not return any rows in my test
environment. However, I would like to pursue it because it is very compact.
Select y.* From tbl1 y
WHERE (SELECT COUNT(*) FROM tbl1 x WHERE x.ID = y.ID) > 1
"Raymond D'Anjou" wrote:

> One possibility (untested):
> Select y.ID, ... From tbl1 y
> WHERE (SELECT COUNT(*) FROM tbl1 x WHERE x.ID = y.ID) > 1
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:832BFBC9-FEF0-4084-8FD1-453D3B7302EE@.microsoft.com...
>
>|||I'm sorry. This did work - worked perfectly!
Select y.* From tbl1 y
WHERE (SELECT COUNT(*) FROM tbl1 x WHERE x.ID = y.ID) > 1
I was actually using a field called RecorID.
Thanks for the code.
"Rich" wrote:
> This solution is interesting, but it did not return any rows in my test
> environment. However, I would like to pursue it because it is very compac
t.
> Select y.* From tbl1 y
> WHERE (SELECT COUNT(*) FROM tbl1 x WHERE x.ID = y.ID) > 1
>
> "Raymond D'Anjou" wrote:
>|||Not neccessarily better Rich.
A lot of times, your first solution will perform better then the join:
Test both solutions (and why not, include mine) in you environment.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:952F58B7-0C3C-4B19-AE82-9AE5CF4D46B1@.microsoft.com...
> And here is the self join I had in mind which appears to work:
> select t1.* from tbl1 t1 join
> (select ID from tbl1 group by ID having count(ID) > 1) t2
> on t1.ID = t2.ID
> This is returning the recordset that I was looking for.
> "Rich" wrote:
>