show ALL duplicate entries

[robschm]

i want to see all duplicate entries of a table.

i tried:

SELECT * FROM name GROUP BY first HAVING COUNT(*) > 1 ORDER BY first


but this shows only the group of all duplicate, but i want to see all duplicate row for row. the problem is, to make a count(*)>1 without a group by. is it possible (i dont think so).

HELP

 

[petersJazz]

Make a self-join:

select a.*
from name a,
name b
where a.first = b.first
and a.anothercolumn > b.anothercolumn;

if you want one of the duplicates

select distinct a.*
from name a,
name b
where a.first = b.first;

if you want all duplicate rows