Dupe Records

Best way to find em!

[bateman_ap]

We are using a MSSQL database and over time have built up quite a database of widgets. However as is usual there is some duplication in there. However, some dupes in names will be right as some of the widgets share names.

What I would really like to do it run something that returns every name that appears at least twice so I can scan through to check and delte as applicable.

Just sorting the records by name in Access although possible really isn't practicle due to the sheer number of records there are.

Must think there is someone who has done this before!

Many thanks

[mattglet]

SELECT COUNT(1) as myCount, yourTable.yourNameColumn FROM yourTable WHERE yourTable.yourNameColumn = 'Green Widget' GROUP BY yourTable.yourNameColumn ORDER BY myCount DESC

That should do it for you.

[bateman_ap]

Thanks, that would only return dupes of "Green Widget" though wouldn't it? Really need something that checks for dupes of each name in the database.

[directrix]

How about:

select namefld, count(*) from file group by namefld having count(*) > 1

That works in generic SQL. Is it possible in MSSQL?

[topr8]

alternatively you could try:

select count(*) as counter,widgetName from widgetTable
group by widgetName order by counter DESC

which will list every title but will show the dupes.

[bateman_ap]

Fab topr8, works perfectly

[mattglet]

Oops, my bad... not sure why I put in that WHERE clause. If I left it out, it would have been fine.