homepage Welcome to WebmasterWorld Guest from 54.211.7.174
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Prune database of all daily records except highest and lowest
clubwager




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

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.

 

syber




msg:4093747
 3:42 am on Mar 9, 2010 (gmt 0)

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)

clubwager




msg:4093786
 7:26 am on Mar 9, 2010 (gmt 0)

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.

syber




msg:4093820
 8:58 am on Mar 9, 2010 (gmt 0)

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)

clubwager




msg:4094299
 9:10 pm on Mar 9, 2010 (gmt 0)

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.

syber




msg:4095020
 10:45 pm on Mar 10, 2010 (gmt 0)

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))

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved