Avatar of PeterErhard
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?
EE1.jpg
EE2.jpg
MySQL Server

Avatar of undefined
Last Comment
PeterErhard

8/22/2022 - Mon
Sharath S

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

ASKER CERTIFIED SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PeterErhard

ASKER
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

PeterErhard

ASKER
Thanks, that got me on the right track and I managed to get a query working.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes