Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: update a field base on order by

    hi
    i have a table like this:
    name--point--rank
    -----------------
    john--16 -- 0
    alis--18--0
    kathy--14--0

    i want to update "rank" filed base on their point, i mean alis rank should be 1, john 2 & kathy 3. (order by point desc).
    is there any way for update this field with a sql statement?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the easiest way is to create a new table
    Code:
    CREATE TABLE newtable
    ( name  VARCHAR(99) NOT NULL PRIMARY KEY
    , point INTEGER NOT NULL
    , rank  INTEGER NOT NULL
    )
    SELECT name
         , point
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE point > t.point ) AS rank
      FROM daTable AS t
    note that's all one statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    144
    Thanx r937, but i wondered why such a common task doesn't have a more straight forward solution in mysql, creating a new table and drop it, everytime i want to update this field is kind of big!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    ...why such a common task doesn't have a more straight forward solution in mysql
    can you show me a better solution in some other database system?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Unfortunately there is no other solution as MySQL does not allow you to use a subselect on the table that you want to update (or delete from or insert into)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait a sec, i shoulda thought of this weeks ago...
    Code:
    SET @rank=0
    ;
    UPDATE newtable
       SET rank = (@rank:=@rank+1)
    ORDER
        BY point DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why store the rank? It would be an ever changing column for the entire table. Why not show the rank, when someone asks for it and on displays. Otherwise, anytime someone moves up/down you have to update entire table again. Does not make much sense to store it that way for me. Also, this is the same complaint you had about Rudy's suggestion of another table.
    Dave

  8. #8
    Join Date
    May 2004
    Posts
    144
    Quote Originally Posted by dav1mo View Post
    why store the rank? It would be an ever changing column for the entire table. Why not show the rank, when someone asks for it and on displays. Otherwise, anytime someone moves up/down you have to update entire table again. Does not make much sense to store it that way for me. Also, this is the same complaint you had about Rudy's suggestion of another table.
    Dave
    Because i want to list user rankings change base on their points in two different stage, and i think it's the only way. And offcourse user points change weekly, so i should update once a week.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •