update a field base on order by

bono56 · #1 (**permalink**) 01-18-11, 15:40

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?

r937 · #2 (**permalink**) 01-18-11, 16:42

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

bono56 · #3 (**permalink**) 01-30-11, 18:00

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!

r937 · #4 (**permalink**) 01-30-11, 18:13

Quote:

Originally Posted by bono56

...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?

shammat · #5 (**permalink**) 01-30-11, 18:19

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)

r937 · #6 (**permalink**) 01-30-11, 18:28

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;

dav1mo · #7 (**permalink**) 01-31-11, 07:48

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

bono56 · #8 (**permalink**) 02-14-11, 04:41

Quote:

Originally Posted by dav1mo

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech