#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89

    Reindexing field in table


    Hello.

    I have topics table.

    Code:
    +-------------+------------------+------+-----+---------+----------------+
    | Field       | Type             | Null | Key | Default | Extra          |
    +-------------+------------------+------+-----+---------+----------------+
    | topic_ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | nav_ID      | int(10) unsigned | NO   |     | NULL    |                |
    | topic_index | int(10) unsigned | NO   |     | NULL    |                |
    | topic_name  | varchar(100)     | NO   |     | NULL    |                |
    +-------------+------------------+------+-----+---------+----------------+
    The topic_index field is used to store sub indexing of the topics which belong to a specific nav_id. Users have the ability move topics up or down in the list.

    There is one particular nav_index value where I want the topics to be indexed alphabetically.

    Writing a PHP routine to reorder the topic_index field would be simple enough but I would like to achive the task within MySQL.

    So in essence if you query the table;

    select * from om_topics where nav_id=3 order by topic_name asc;

    I wold like to update topic_index = rowNum from the previous select query.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89
    Thanks,

    I figured it out.


    Code:
    update
    	om_topics t, 
    	(select 
    		@rownum:=@rownum+1 'rank', 
    		om_topics.topic_id 
    	from 
    		om_topics, 
    		(SELECT @rownum:=0) r 
    	where 
    		nav_ID=3 
    	order by 
    		topic_name asc) r 
    set 
    	t.topic_index = r.rank 
    where 
    	t.topic_ID=r.topic_ID;
    Last edited by TASB; October 11th, 2012 at 02:03 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    Thanks for the help TASB. I think I am getting close but I am still not quite there... any help would be most appreciated:



    UPDATE PlayerRankings p,
    (SELECT @rownum := @rownum + 1,
    @rank := IF(@prev_val!=Score,@rownum,@rank) AS rank,
    @prev_val := Score
    FROM
    p,
    (SELECT ((Select @rownum := 0),
    (SELECT @rank := 1) ,
    (SELECT @prev_val = NULL)) AS ra)
    WHERE
    idGame = 1
    ORDER
    BY Score DESC)
    SET p.Rank = ra.rank;


    Error Code: 1248. Every derived table must have its own alias 0.015 sec


    I looked up the error but I don't think I grasp the concept of it with this.

    Thanks,
    Ryan
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by rdubya
    I looked up the error but I don't think I grasp the concept of it with this.
    a derived table is a subquery in the FROM clause

    here's a derived table with an alias --

    (SELECT @prev_val = NULL) AS ra

    here's one without --

    (Select @rownum := 0)

    Comments on this post

    • rdubya agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    Hmm.. I tried setting all the tables as derived:::

    UPDATE PlayerRankings p,
    (SELECT @rownum := @rownum + 1 AS rownumber,
    @rank := IF(@prev_val!=Score,@rownum,@rank) AS rank ,
    @prev_val := Score AS score
    FROM
    p,
    (SELECT ((Select @rownum := 0 AS test2) ,
    (SELECT @rank := 1 AS test) ,
    (SELECT @prev_val = NULL AS prevscore)) AS ra)
    WHERE
    p.Game_idGame_FK = 1
    ORDER
    BY p.Score DESC)
    SET p.Rank = ra.rank;

    Still getting the same error.... Am I not setting them properly?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    157
    Rep Power
    89
    Originally Posted by rdubya
    (SELECT ((Select @rownum := 0 AS test2) ,
    (SELECT @rank := 1 AS test) ,
    (SELECT @prev_val = NULL AS prevscore)) AS ra)
    I think your problem lies here.

    Try

    Code:
    (SELECT 
    	@rownum := 0 AS test2,
    	@rank := 1 AS test,
    	@prev_val = NULL AS prevscore) AS ra;
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    Same error =( thanks for trying though-- any other ideas?


    UPDATE PlayerRankings p,
    (SELECT @rownum := @rownum + 1 AS rownum,
    @rank := IF(@prev_val!=Score,@rownum,@rank) AS rank ,
    @prev_val := Score AS score
    FROM
    p,
    (SELECT @rownum := 0 AS test2,
    @rank := 1 AS test,
    @prev_val = NULL AS prevscore)AS ra
    WHERE
    p.Game_idGame_FK = 1
    ORDER
    BY p.Score DESC)
    SET p.Rank = ra.rank;

IMN logo majestic logo threadwatch logo seochat tools logo