My head hurts
Posted: Sun Jul 22, 2007 4:51 pm
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:
And it returned the following:
My logic was somewhere like this:
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.
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 , 30We 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?".cnt
492
176
157
139
121
115
112
111
110
106
103
etc...
My logic was somewhere like this:
Then I realized a few things;DELETE FROM rg_iplog WHERE (SELECT count(*) FROM rg_iplog WHERE id = id) > 10
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.