Display ALL Duplicate records in Table
Posted on 2007-11-28
I am currently using the HAVING method to select duplicate records as follows:
SELECT MachineName, COUNT(*) AS 'Duplicate Count' FROM [NICresults]
GROUP BY MachineName
HAVING ( COUNT(*) > 1 )
which produces these results:
MachineName | Duplicate Count
Machine01 | 2
Machine06 | 3
Machine34 | 2
So far so good, but what I need to do now is display all the records and not just what is duplicated. I have a table with all my machines and the NICs installed in each. Some Machines have a single NIC and some have multiple NICs. I need a list of machines with multiple NICs and the actual records which include the NIC details for each.
I am thinking there must be a way to compare the results of the first SELECT to create a SELECT that pulls only the records that have those results. but again I am unsure how to do it.
any help would be greatly appreciated