Select duplicates 1

[MikeRBS]

How do you select those rows which have a value that appears also in another row ie something like Select EMPNO, Firstname, Secondname where Count(Secondname >1)?

I want to list the details of items that are probably duplicates.

 

[PHV]

A starting point:
SELECT EMPNO, Firstname, Secondname
FROM yourTable A
WHERE (SELECT Count(*) FROM yourTable B WHERE B.Secondname=A.Secondname)>1;
Another way:
SELECT A.EMPNO, A.Firstname, A.Secondname
FROM yourTable A INNER JOIN (
SELECT Secondname FROM yourTable GROUP BY Secondname HAVING Count(*)>1
) B ON A.Secondname=B.Secondname;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikeRBS]

Many thanks PH.

I initially solved this by creating a table of duplicates (using a grouped query) then joining from that table back to the main table to pick up the detail lines.

However I'm now doing a similar thing again and your first method works fine. I was stumped for a little while until I realised it was yourTable A, not yourTableA.