Link to home
Start Free TrialLog in
Avatar of PeterErhard

asked on

Position Updating

I have the following table (see attachment 1).

It contains a summary view containing users points per CompetitionRoundID.

What I need to be able to do is calculate the Position for each round (CompetitionRoundID), so it would finish up like attachment 2 which I've updated manually.

How could I have an update query loop through all available CompetitionRoundIDs and do this?
Avatar of Sharath S
Sharath S
Flag of United States of America image

try this query.
UPDATE MyTable T1 
       JOIN (SELECT *, 
                    IF(@CompetitionRoundID = CompetitionRoundID, @rownum := @rownum + 1, @rownum := 1)            AS RowNum,
                    IF(@CompetitionRoundID <> CompetitionRoundID, @CompetitionRoundID := CompetitionRoundID, 999) s
             FROM   MyTable, 
                    (SELECT @rownum := 1, 
                            @CompetitionRoundID := 999) r 
             ORDER  BY CompetitionRoundID, 
                       Points DESC) T2 
         ON T1.CompetitionRoundID = T2.CompetitionRoundID 
            AND T1.Points = T2.Points 
SET    T1.Posiiton = T2.Posiiton; 

Open in new window

Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterErhard


Thanks Sharath, it updates 0 zeros though when it should update 8.

Also, what is the 999 there out of interest? Is that some limit? If so, what would happen if there finishes up with more than 999 rounds in the table?

Here is the data and table structure
CREATE TABLE IF NOT EXISTS `CompetitionRoundsUserPoints` (
  `CompetitionRoundsID` int(10) NOT NULL,
  `UserID` int(10) NOT NULL,
  `Points` mediumint(7) NOT NULL,
  `Position` mediumint(7) NOT NULL,
  PRIMARY KEY (`CompetitionRoundsID`,`UserID`),
  KEY `UserID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- Dumping data for table `CompetitionRoundsUserPoints`

INSERT INTO `CompetitionRoundsUserPoints` (`CompetitionRoundsID`, `UserID`, `Points`, `Position`) VALUES
(1, 7, 60, 0),
(1, 8, 240, 0),
(1, 10, 30, 0),
(1, 11, 495, 0),
(2, 7, 20, 0),
(2, 8, 29, 0),
(2, 10, 95, 0),
(2, 11, 121, 0);

Open in new window

Thanks, that got me on the right track and I managed to get a query working.