Count and delete oldest entries

[acctman]

Hi I need help with a query that will cycle through the 'com_for' (userid) field and then only keep the latest 200 comments and delete everything after. So if 'com_for' userid 39 has 250 comments then delete the oldest 50 comments. The com_time field holds the time in this format 1133173361

 

table: rate_picture_comments

fields: com_for , com_comment , com_time

 

thanks in advance. this is a bit too advanced for me to figure out how to do without some assistance.

[revraz]

You could try something like

 

DELETE FROM `table` WHERE `com_for` = 39 AND `com_time` = (SELECT `com_time` FROM `table` ORDER BY `com_time` DESC LIMIT 200, 18446744073709551615;

 

Or you can use a SELECT COUNT statement to count how many rows there are and use that number as your Max.

[kickstart]

Hi

 

An alternative for you:-

 

DELETE 
FROM rate_picture_comments 
WHERE com_for = 39 
AND com_comment NOT IN (SELECT COM_COMMENT 
FROM rate_picture_comments 
WHERE com_for = 39 
ORDER BY com_time DESC 
LIMIT 200)

 

Or to any older ones for all "com_for":-

 

DELETE FROM rate_picture_comments 
WHERE com_comment NOT IN (SELECT COM_COMMENT 
FROM rate_picture_comments a 
WHERE COM_COMMENT IN (SELECT COM_COMMENT 
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200))

 

Not directly tested either but think you can see the method.

 

It would be rather better if the rate_picture_comments had a unique key.

 

All the best

 

Keith

[acctman]

Hi

 

An alternative for you:-

 

DELETE 
FROM rate_picture_comments 
WHERE com_for = 39 
AND com_comment NOT IN (SELECT COM_COMMENT 
FROM rate_picture_comments 
WHERE com_for = 39 
ORDER BY com_time DESC 
LIMIT 200)

 

Or to any older ones for all "com_for":-

 

DELETE FROM rate_picture_comments 
WHERE com_comment NOT IN (SELECT COM_COMMENT 
FROM rate_picture_comments a 
WHERE COM_COMMENT IN (SELECT COM_COMMENT 
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200))

 

Not directly tested either but think you can see the method.

 

It would be rather better if the rate_picture_comments had a unique key.

 

All the best

 

Keith

 

 

com_id is the unique index key

[kickstart]

Hi

 

Wish that was in the original post .

 

Try this

DELETE FROM rate_picture_comments 
WHERE com_id NOT IN (SELECT com_id 
FROM rate_picture_comments a 
WHERE com_id IN (SELECT com_id 
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200))

 

Give that a try (but back things up first and test thoroughly).

 

All the best

 

Keith

[acctman]

Hi

 

Wish that was in the original post .

 

Try this

DELETE FROM rate_picture_comments 
WHERE com_id NOT IN (SELECT com_id 
FROM rate_picture_comments a 
WHERE com_id IN (SELECT com_id 
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200))

 

Give that a try (but back things up first and test thoroughly).

 

All the best

 

Keith

 

this is the error i received

MySQL said: 

 

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

 

I'm running mysql 4.1.22 and I ran the code in phpmyadmin

[kickstart]

Hi

 

Seems it isn't supported (yet).

 

However there might be a work around. Not tried this but a quic search suggests that while you cannot use a limit in an IN clause, you migth be able to use it like this:-

 

DELETE FROM rate_picture_comments 
WHERE com_id NOT IN (SELECT com_id 
FROM rate_picture_comments a 
WHERE com_id IN (SELECT com_id FROM (SELECT com_id
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200) AS SubSelect))

 

All the best

 

Keith

[acctman]

Hi

 

Seems it isn't supported (yet).

 

However there might be a work around. Not tried this but a quic search suggests that while you cannot use a limit in an IN clause, you migth be able to use it like this:-

 

DELETE FROM rate_picture_comments 
WHERE com_id NOT IN (SELECT com_id 
FROM rate_picture_comments a 
WHERE com_id IN (SELECT com_id FROM (SELECT com_id
FROM rate_picture_comments b 
WHERE b.com_for = a.com_for 
ORDER BY com_time DESC LIMIT 200) AS SubSelect))

 

All the best

 

Keith

 

received this error

MySQL said: 

 

#1109 - Unknown table 'a' in where clause

 

i think you're getting close. should a.com_for be a.com_id?

[kickstart]

Hi

 

It should. Sorry.

 

However not sure how to get rid of it complaining about table a being unknown.

 

All the best

 

Keith

 

[acctman]

Hi

 

It should. Sorry.

 

However not sure how to get rid of it complaining about table a being unknown.

 

All the best

 

Keith

 

 

ok i was reading up on that error, and in some cases people suggest off loading some of the process to a scripting language like PHP and looping through the field entries

[kickstart]

Hi

 

I suspect that is what you might be forced to do, which would make it far less efficient.

 

All the best

 

Keith