Forum Moderators: open
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.
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!