Finding duplicates in SQL 2005

[vcujackson]

I have the following code

select id,date,class
from field1
having count(id) >1

My desired output is
ID date class
11 9/15/09 c
11 5/1/09 d

The only way an id should appear more than once is if it has multiple classes for the same id. Else I do not want it to appear. So a full listing looks like
ID date class
11 9/15/09 c show
11 5/1/09 d show
12 12/15/09 c not show
14 9/10/09 c not show

So I want the first two to show not the last two. Essentially I am counting to see if the rows have the same value. If so I want to list them. If not I want to eliminate them

 

[r937]

SELECT field1.id
     , field1.date
     , field1.class
  FROM ( SELECT id
           FROM field1
         GROUP
             BY id
         HAVING COUNT(*) > 1 ) AS dups
INNER
  JOIN field1
    ON field1.id = dupes.id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

[link]
;with CTE_Dups as (select ID from myTable group by ID having count(Class) > 1)

select T.* from myTable T inner join Cte_Dups C on T.ID = C.ID[/url]