Find duplicates, display each result in sql

Question

I want to write something like this :

select t.id, t.name, from table t
group by t.name having count(t.name) > 1

To produce the following :

id      name  count 
904834  jim    2
904835  jim    2
90145   Fred   3
90132   Fred   3
90133   Fred   3

Answer 1

For SQL Server 2005+, you can do the following:

SELECT *
FROM (SELECT id, Name, COUNT(*) OVER(PARTITION BY Name) [Count]
      FROM table) t
WHERE [Count]>1

Answer 2

If you remove the ID column then you can get all the names that have multiple entries

select t.name
from table t
group by t.name
having count(t.name) > 1

For each name, if you want the minimum or maximum id you can do this

select t.id, t.name, min (t.id) as min_id, max (t.id) as max_id
from table t
group by t.name
having count(t.name) > 1

For each name, if you want all the ids that are duplicates, then you have to use a subquery

select t.id, t.name
from table t
where name in
(
    select t1.name
    from table t1
    group by t1.name
    having count(t1.name) > 1
)