Kim Mackey wrote:
Group,
I have been working on a project for a while now trying to figure out
how to remove duplicate records from a single table using a query. To
complicate matters, what constitutes a duplicate record is a match on
several fields, but not all fields. I have been successful in matching
all duplicates based on the fields I'm interested in, but this only
returns the duplicate records. I want a query that will return all
records from the table, but only once if certain fields are the same.
The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then some other fields
I don't want to use the values in the other fields to determine if the
record duplicates another. I'm not concerned about which one of the
duplicate records I keep because I will make a backup copy of this table
before removing the duplicates, so I will still have all the data from
the records deleted. Later I will redesign the tables for this database
and link back in the data from the other fields.
So I need a method that will output one and only one record from the
table in which the data in the above mentioned fields are the same.
Again, even if the data in the remaining fields are different I just
want one of the records, and I don't care which one.
CREATE TABLE `new_table`
SELECT *
FROM `old_table`
GROUP BY [your_fields_you_want_unique];
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]