The Database Forum

**alf** · 11:55 AM

I've seen a lot of queries to find duplicates in a database, but after trying and failing for a long time now, i cant make my own spesific case

ExTable:

Code:

-----------------------------------------
id     name    item_id   not_important
-----------------------------------------
1      tom       4          6345
2      tom       6            42
3      tom       7           789
4      tom       4           234
5      tom       6           234
6      tom       4           365
7      tom       3            34
8      eric      3           365
9      eric      3            34

I want a result like this:

Code:

--------------------------------
 name    item_id   count(x)
--------------------------------
 eric      3          2
 tom       4          3
 tom       6          2
 tom       3          1
 tom       7          1

what would be the query?

**alf** · 06-18-2009, 12:17 PM

Found it!

Code:

SELECT name,item_id,count(*) FROM example1 GROUP BY name, item_id HAVING count(*)>1

**vikas1234** · 06-25-2009, 03:48 AM

use the distinct on field for which you want to get unique value