MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Don Read Date:February 10 2004 6:18pm
Subject:Re: A "current row updating" counter for a mass "Update" or similar?
View as plain text  
On 10-Feb-2004 admin@stripped wrote:
> Hi all, I'll just get straight to the problem (simplified):
> 
> Say I have the following table/columns:
> ID, Score, Rank
> 1, 20, 0
> 2, 50, 0
> 3, 10, 0
> 4, 5, 0
> 5, 97, 0
> 
> Is it possible, in a single query (using sub-queries if necessary),
> to give
> all of these "ID's" the correct rank... ie if such a thing existed it
> might

<snip>

SET @r:=0;
SELECT ID, score, (@r:=@r+1) AS rank FROM teams ORDER BY score DESC, ID;
+------+-------+------+
| ID   | score | rank |
+------+-------+------+
|    5 |    97 |    1 |
|    2 |    50 |    2 |
|    1 |    20 |    3 |
|    3 |    10 |    4 |
|    4 |     5 |    5 |
+------+-------+------+

<snip>

> I can't seem to find any obvious nice way of doing this, other than
> the poor
> method of looping/iterating through with an "Ordered" select, and
> then
> sending individual "UPDATES" for each 

Hint: CREATE TEMPORARY TABLE


Regards,
-- 
Don Read                                       dread@stripped
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
Thread
A "current row updating" counter for a mass "Update" or similar?admin10 Feb
  • Re: A "current row updating" counter for a mass "Update" or similar?Don Read10 Feb
RE: A "current row updating" counter for a mass "Update" or similar?emierzwa10 Feb
  • Re: A "current row updating" counter for a mass "Update" or similar?admin10 Feb