My head hurts

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

My head hurts

Post by d3ad1ysp0rk »

I'd make a better title, but "Join issues" or "Subquery problems" wouldn't work either, because I don't know if they're even suitable for this.

I have a table, it is an ip log.
userid [int]
ip [varchar]
logdate [timestamp]

I just ran the following:

Code: Select all

SELECT count( * ) AS cnt
FROM `rg_iplog`
GROUP BY userid
ORDER BY cnt DESC
LIMIT 0 , 30
And it returned the following:
cnt
492
176
157
139
121
115
112
111
110
106
103
etc...
We only really use 10 ips per user. I'm thinking "how am I going to shrink this down to where 10 is the largest cnt that will be there?".

My logic was somewhere like this:
DELETE FROM rg_iplog WHERE (SELECT count(*) FROM rg_iplog WHERE id = id) > 10
Then I realized a few things;
1) It will delete all rows if it works.. not just enough to make it 10. I want to save the latest 10 (order by log_date DESC)
2) How would you specify to use the id to use (as you can see, id=id won't work).
3) No subqueries on the same table according to mySQL.

I'm at a loss. Any help would be appreciated.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

It would be a lot easier if you told us what the primary key is...

1-) Select for each user the 10 most recent entries

Code: Select all

SELECT *FROM rg_iplog AS a WHERE %PK% IN (
 SELECT %PK% FROM rg_iplog AS b WHERE a.userid = b.userid ORDER BY logdate DESC LIMIT 10
) ORDER BY userid ASC;
2-) Now you want to select (or delete) all the rows that are not in this set...

Code: Select all

DELETE FROM rg_iplog WHERE %PK% NOT IN (
  SELECT *FROM rg_iplog AS a WHERE %PK% IN (
   SELECT %PK% FROM rg_iplog AS b WHERE a.userid = b.userid ORDER BY logdate DESC LIMIT 10
 )
)
Post Reply