Select serialNo, count(serialNo) serNoCount from table group by serialNo
order by 2 desc
This will give you a list of all the unique serial numbers in your table.
The ones with serNoCount>1 are your duplicates. Without the capability to
do subselects (yes, we all know...it's coming in the mythical v4!) you'll
have to process that list in something like Java or PHP.
HTH,
Cal
http://www.calevans.com
-----Original Message-----
From: Josh Burroughs [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 14, 2001 6:48 PM
To: [EMAIL PROTECTED]
Subject: hunting down duplicate entries
We recently moved our hardware inventory database off of access (yay!)
over to mysql. During it's days as an access database it aquired a
numbered of duplicate entries. In every case (or at least the ones I'm
working on) the serial numbers will be the same but likely all other info
will be slightly different. One of the records will be basically correct
and current, the other faulty. I want to be able to find every case in the
database where more than one record shares the same serial number (which
is not at present a key feild, but will be once the dupes are removed) so
we can look at the records and determine which ones are valid and which
ones need to go. Is there a way to this?
thanks in advance
- Josh
"Listen: We are here on Earth to fart around. Don't let anybody tell you
any different!" - Kurt Vonnegut
Josh Burroughs
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
