Forum Moderators: open

Message Too Old, No Replies

Delete all daily records except highest and lowest?

I want to prune my database of all the unneeded entries.

         

clubwager

3:39 am on May 22, 2008 (gmt 0)

10+ Year Member



How do I delete all daily records except highest and lowest?

I have a mysql database that has ammassed the numbers of poker players at several online poker sites. It has an entry every 15 to 30 minutes since May 2006. All I need to retain is the highest and lowest numbers for each day. I need help to write a query to delete everything except the highest and lowest number each day.

I can get the highest and lowest numbers of players for an interval (a year in this example) with this query:

SELECT max( num_players ) , min( num_players )
FROM table
WHERE updated >= date_sub( now( ) , INTERVAL 1 year )

But, I am stumped as to how to get everthing except the highest and lowest of each day.

Any help or ideas will be appreciated.

coopster

12:22 am on May 24, 2008 (gmt 0)

WebmasterWorld Administrator 10+ Year Member



Welcome to WebmasterWorld, clubwager.

I think you are going to need to incorporate subqueries. Have a look at the EXISTS [dev.mysql.com] manual page to get some ideas to get started. BTW, make a good backup before you start playing with DELETE queries!