Copying Only Unique Records From One Table To Another

[curtiss]

Please bear with me, as I hope this isn't a a question that I could have easily answered if I had searched with the correct terminology. I tried searching quite a bit, and never came up with an answer that worked for me.

Anyway, onto my question:

Within the job-tracking application I'm developing, I had one table that kept quite a bit of information about our customers, and the specific job we did for that customer. However, I have since decided that I want to keep the customer information in a separate table from the information about the specific job we did for them.

Therefore, I need to find a way to easily copy all of the customer contact info from the old table into the new table. However, being that there are quite a few customers for whom we've done multiple jobs, there will be quite a bit of duplicate entries.

However, you have to keep in mind that only a few of the columns within those duplicate rows will actually be duplicates.

For instance, let's say I have the following columns in my existing table:
Customer Name
Customer Address
Customer Phone
Job Performed
Price of job
Date of job

The Customer Name, Customer Address and Customer Phone should all be duplicated for each job we did for the customer. However, the job performed, price and date will obviously be different.

So, is there an easy way to use PHP or MySQL to automatically go through a table and ignore rows that have one particular column that's duplicated?

I hope I made my question clear. I'll keep doing research, but I thought I'd ask at this point, as I hadn't had much luck with my research up until now.

EDIT - After doing a little more research, and seeing some other people that have asked similar questions, I see that I need to include a little more information about what I want to do.

I should go back to the example I posted above, but alter my description a little bit.

Let's say that I have the following columns in my table:
Customer Name
Customer Address
Customer Phone
Job Description
Job Price
Job Date

Now, let's say that the only thing that's going to be duplicated is the Customer's name, but not their address or phone number. Being that I don't really care what the customer's old address is, I really only want to copy the latest recordset for that customer's name.

So, what I would want to do is select the entire Customer Name column in my table, then, if duplicates are found, I would want to figure out which one is the newest (which would most likely need to be derived from the Job Date), and then copy the Customer Name, Customer Address and Customer Phone from the original table into the new table.

[Deadeye]

the unique or distinct keyword you solve your problem.

I am not 100% sure on the syntax and I dont have any data that I can easily test this on.

but I think it would be along these lines

select unique(Customer_name), (rest of the fields) from tableName

I have had to use this before and it should work, if this doesnt work then try distinct.

[Till]

Hmm, if you don't need a single query to perform it, I'd do something like this:

SELECT count(*) AS total_records, unique_value, id
FROM old_table
WHERE HAVING total_records = 1
GROUP BY unique_value

This *should* get you the IDs.

Then...

CREATE TABLE new_table
SELECT * FROM old_table WHERE id IN (..., ..., ...)

You could also put this into a stored procedure, if your MySQL(5) supports it.