From: Andy Wallace
Date: February 19 2008 4:53pm
Subject: Re: MYSQL Limit
List-Archive: http://lists.mysql.com/mysql/211408
Message-Id: <47BB097D.7020508@cisdata.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Looks like you're missing a comma after "comm_id", before
the @num :=  line?
andy

Santosh Killedar wrote:
> I am trying the following code on 4.1.2 and getting a
> syntax error that I could not figure out. It works
> fine on 5.x. Any suggestion/alternate
> 
> CREATE TEMPORARY TABLE Temp
> (Node INT,
>  comm_id INT, INDEX USING BTREE (comm_id))
>   ENGINE = MyISAM;
> 
> INSERT INTO Temp
> SELECT recipient, id
> FROM `main_guestbook` 
> ;
> 
> set @Node := '', @num := 1;
> delete from main_guestbook where id in (
> select comm_id
> from (
>    select Node, comm_id
>        @num := if(@Node = Node, @num + 1, 1) as
> row_number,
>        @Node := Node as dummy
>    from Temp order by Node, comm_id desc
> ) as x
> where row_number > 500
> )
> 
> MySQL said:  
> 
> #1064 - You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server
> version for the right syntax to use near '@num := if(
> @Node = Node , @num + 1 , 1 ) as row_number ,  @Node
> := Node as dumm' at line 1 
> 
> 
> 
> ---------------------------------------------
> 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
> 
> 
> 
> 
>       ____________________________________________________________________________________
> Looking for last minute shopping deals?  
> Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping
> 

-- 
Andy Wallace - CISData - IDX Slave
AIM: acmwallace   awallace@stripped