Welcome to WebmasterWorld Guest from 54.145.166.96

Forum Moderators: open

Message Too Old, No Replies

Prune database of all daily records except highest and lowest

   
3:44 am on Mar 7, 2010 (gmt 0)

5+ Year Member



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

I have a mysql database that has amassed the numbers of poker players at several online poker sites (over 400,000 rows now!). 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.
3:42 am on Mar 9, 2010 (gmt 0)

10+ Year Member



SELECT *
FROM TABLE
WHERE updated >= date_sub( now(), INTERVAL 1 year) AND
num_players > (SELECT MIN(num_players FROM table) AND
num_players < (SELECT MAX(num_players FROM table)
7:26 am on Mar 9, 2010 (gmt 0)

5+ Year Member



It caused an error . I added ) to balance

num_players > (SELECT MIN(num_players FROM table)) AND
num_players < (SELECT MAX(num_players FROM table))

No error but it returns empty result set.

Thanks for the suggestion.
8:58 am on Mar 9, 2010 (gmt 0)

10+ Year Member



Sorry for the missing parens:

SELECT *
FROM TABLE
WHERE updated >= date_sub( now(), INTERVAL 1 year) AND
num_players > (SELECT MIN(num_players) FROM table) AND
num_players < (SELECT MAX(num_players) FROM table)
9:10 pm on Mar 9, 2010 (gmt 0)

5+ Year Member



Thanks for your help. By adding the parens it works, but it returns the same as if I leave off the last two lines. I fiddled with it though and came up with something interesting.

First of all, I think I left out something important. I failed to make it clear that I need to prune each site seperately and I need to also keep the highest and lowest table numbers each day too. I am sorry for this deficiency. The table has only 5 columns: item_id, site, num_players, num_tables and updated. There are 11 different sites.

So, I need to delete everything except highest and lowest num_players and highest and lowest num_tables for each site and for each day.

I modified your query to:

SELECT * 
FROM minmax
WHERE site = 'Poker Stars' AND updated >= date_sub( now(), INTERVAL 1 day) AND
num_players > (SELECT MIN(num_players) FROM minmax WHERE site = 'Poker Stars' AND updated >= date_sub( now(), INTERVAL 1 day)) AND
num_players < (SELECT MAX(num_players) FROM minmax WHERE site = 'Poker Stars' AND updated >= date_sub( now(), INTERVAL 1 day))


This gets num_players between highest and lowest for Poker Stars for the past 24 hours. So, it is on the right track. I need it to cycle through each day for the past x years and return this same data for each day (so I can delete it and be left with only the highest and lowest for each day). Ideally, it should not delete the highest and lowest num_tables though, and it should do the query on each site. It seems very complicated.

Thanks for your efforts. I really appreciate any attempts or ideas you may have.
10:45 pm on Mar 10, 2010 (gmt 0)

10+ Year Member



Adding this to the end should get you closer:

AND
num_tables > (SELECT MIN(num_tables) FROM minmax WHERE site = 'Poker Stars' AND updated >= date_sub( now(), INTERVAL 1 day)) AND
num_tables < (SELECT MAX(num_tables) FROM minmax WHERE site = 'Poker Stars' AND updated >= date_sub( now(), INTERVAL 1 day))
 

Featured Threads

Hot Threads This Week

Hot Threads This Month