0o0o0 Posted October 5, 2008 Share Posted October 5, 2008 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.. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 5, 2008 Share Posted October 5, 2008 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) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.