Query to return full row where there is a Duplicate column

[ppitch]

Hi,

I am working on a program where I need to return every row that contains a duplicate serial number.

I have this working but it only returns the serial number and the count where as I need to return each row that the duplicate appears on.

I have been informed I need to join this query back to the original table on serial but cannot work out how to do it.

SELECT serial,
COUNT(serial) AS NumOccurrences
FROM address
GROUP BY serial
HAVING ( COUNT(serial) > 1 )


Any help would be greatly appreciated.

 

[bborissov]

SELECT address.*
FROM address
INNER JOIN (SELECT Serial
            FROM address
            GROUP BY Serial
            HAVING (COUNT(serial) > 1 )) Tbl1
      ON Address.Serial = Tbl1.Serial

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ppitch]

Thanks Borislav,

I have tried this but it seems to return the whole table and not rows with duplicate serial numbers.

I will try modifiying this and see where I get.

 

[markros]

In SQL 2005+ you can also do

;with cte as (select *, count(*) over (partition by Serial) as NumOccurrences from Address)

select * from cte where NumOccurrences >=2

PluralSight Learning Library

 

[ppitch]

Thanks

I will give this a go and hopefully you have fixed my issue.