replace duplicates by chosing the newer timestamp

[0o0o0]

Im assuming a tmp table is needed so I made that... 

 

Ok so ( nfl as example again) ..

 

I made it possible to import csv file into NFL TABLE... then delete all rows with other players... except for brett farve and dan marino and say payton.

 

so now I have NFL TABLE with .. just three rows.. brett farve and dan marino and payton.

 

Let assume ... marino still plays .. but tomorrow he'll retire.. although we want his LAST csv data to stick..

 

 

tomorrow cron will run bring the new csv info  ... and dan marino isnt in the datafile anymore..

 

so todays info for marino will stay in the db.. but be his last...

 

so it runs it process... and the NEW payton and farve data came in..

 

Now... heres the part..  I have todays payton, farve and marino rows..

and also tommorrow in the db.. so now 5 rows..

 

those 5 rows have timestamps...

 

I want to delete duplicates by the player.. plus by the oldest times of the two...

 

so payton 10:00pm tomorrow

    payton  10:00pm  today

 

drops todays out of the db.. and keeps tomorrows.

 

all the while keeping marinos row cause it is not a duplicate.. and will remain in the db forever as his last..

 

get me?

 

****  basically how to delete duplicate rows by PLAYER_NAME  and the OLDEST of the two timestamps.

 

 

lol sorry for the long example..

 

 

[Barand]

this will select the data from the nfl rows that contain the newest date for each name

 

SELECT name, tstamp, othercols

FROM nfl n

JOIN (SELECT name, MAX(tstamp) as tstamp FROM nfl GROUP BY name) as x

USING (name,tstamp)