#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    52
    Rep Power
    14

    Adding a line to reset rank back to 1


    This is how I rank the players in tournaments that I host:

    Code:
    SELECT
        IF(@LastScore <> Score, @CurrentRank := @CurrentRank + @NextRank, @CurrentRank) AS Rank, Player, Games,
        IF(@LastScore = Score, @NextRank := @NextRank + 1, @NextRank := 1) AS AddToGetNextRank,
        @LastScore := Score AS Score, @LastGames := Games AS Games
    FROM
        Results
    JOIN
        (SELECT @CurrentRank := 1, @NextRank := 0, @LastScore := 0) p
    WHERE
        Date='September 14'
    ORDER BY
        Score DESC

    What I would like to add to this query is the ability to reset the CurrentRank to 1 if "Games" changes.

    Because the data is ORDERed BY Score DESC, it is guaranteed that Games never increases from row n to row n+1. In other words, Games will decrease or stay the same.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    52
    Rep Power
    14
    I was thinking about a line:

    Code:
    IF(@LastGames > Games, @CurrentRank := 1, @NextRank := 1) AS ResetRank,
    But I couldn't get it to work...

IMN logo majestic logo threadwatch logo seochat tools logo