Wow !! Thank you. I was not aware of this construct and seems to be on the right path.
Just noticed a few thing as the query below was returning 0 records. 1. Shouldn't the last line be (@last < @keeplast) 2. If the above 1 is correct, Where the @last should get set to zero again? For some reason it is not getting zero again and even for the very first set of NodeID, its is not stopping after reaching keeplast. Really appreciate your help. Santosh --- joe <[EMAIL PROTECTED]> wrote: > Using Baron's SQL you may want to test with > something like this where you > set @keeplast to the Threshold you want to keep > > set @keeplast := 20, @last := 0; > select NodeID, CommentID > from > (select NodeID, CommentID, lastupdate from t > order by NodeID, lastupdate desc) a > where > (@last := if(NodeID = @NodeID, @last + 1, 1)) is > not null > and (@NodeID := NodeID) is not null > and (@last > @keeplast) > ; > > > -----Original Message----- > From: joe [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 16, 2008 8:12 PM > To: 'Santosh Killedar' > Subject: RE: MYSQL Limit > > http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques > / > > > > -----Original Message----- > From: Santosh Killedar [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 16, 2008 5:53 PM > To: mysql@lists.mysql.com > Subject: MYSQL Limit > > I have a MYsql table with following columns Node ID, > Comment ID, Text, Date. > Coment ID is primary key. For each Node ID there are > one or more comment IDs > (comments). There is a threshold (max_comments) that > a node can have. How > can I delete oldest comments associated with those > nodes where this > threshold is surpassed, such that the number of > comments again will be below > threshold for the node. The threshold is common for > all nodes but the > current number of comments is not (since those were > added before the > threshold policy established). Please note I want to > delete older comments > first, only for nodes above threshold > > thanks > Santosh Killedar > > > > ____________________________________________________________________________ > ________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.516 / Virus Database: 269.20.7/1283 - > Release Date: 2/16/2008 > 2:16 PM > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.516 / Virus Database: 269.20.7/1283 - > Release Date: 2/16/2008 > 2:16 PM > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.516 / Virus Database: 269.20.7/1283 - > Release Date: 2/16/2008 > 2:16 PM > > > ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
