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.
