Help With Query?

[Wynder]

Greetings!

I have a table of data that I've imported from another ODBC source and the field I'm going to using as the primary key has some duplicate values.

So, I need help building a query that will pull any data that doesn't have duplicate values in the table or, if it does have a duplicate value it will pull the one whose date is most recent.

Any thoughts?

[weekender]

thoughts as follows

1. get a list of records which have duplicates

SELECT DISTINCTROW(id), COUNT(id) FROM table WHERE 1

2. Add a temp field into the db (don't have to, just makes things easier). Then go through the list of returned id's, and for each one run this

SELECT * FROM table WHERE id='$id' ORDER BY DATE ASC LIMIT 1

this will take the oldest record for each duplicate (only one record per duplicate because of LIMIT 1)

you can change this last sql into a delete statement to automatically delete the oldest of each duplicate, or you can have i mark the temp field and delete later after an audit.

as it only deletes the oldest of each dupe, you may want to check how many rows are returned by the first sql (mysql_num_rows) and run it a few times until there are no more dupes.

try on test data first. Let me know if this works, i'd be really astounded!

adam

[Wynder]

Thanks for the input, but I wound up doing something a bit earlier...

I imported tables from Oracle 8i into Access 2003. Copied the structure of the table only to a new table and made the field I needed to be a primary key.

Then, created an append query sorted descending by date to append into the new table.

From there, I was able to Export into MySQL.

God, I hate migrations. 🙂