Duplicate Records was wondering

[realtime158]

Here is a SQL statement that will count all the duplicate instances of the title

SELECT SID,title,
COUNT(title) AS NumOccurrences
FROM savedsearche where post_id <> ''
GROUP BY title
HAVING ( COUNT(title) > 1 ) order by NumOccurrences desc

Now what i want to do is move all the duplicated instances to another table but keep only one
instance of the record in table where the duplicateds were found. How would you do this using mySQL

Thanks...

[chazzy]

is there any date/time field showing which was inserted first?

[realtime158]

Yes there is a data the field is called datecreated

Thanks...

[chazzy]

select * from table a WHERE datecreated != (select max(datecreated) from table WHERE title = a.title)

What this does is select all columns of all items in a table that don't have the max value of datecreated.

[realtime158]

I am not using mySQL 5.X but only 4.0.27-standard. This statment will not work.

Thanks...

[chazzy]

convert it to an outer join, should work the same.

[realtime158]

select * from table a WHERE datecreated != (select max(datecreated) from table WHERE title = a.title)

So how would you do that because the data is coming form the same table.