Set rank based on multiple columns?











up vote
8
down vote

favorite
3












I have a table which has 3 fields, I want rank column based on user_id and game_id.



Here is SQL Fiddle :
http://sqlfiddle.com/#!9/883e9d/1



the table already I have :



 user_id | game_id |   game_detial_sum  |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |


expected output :



user_id  | game_id |   game_detial_sum  |  user_game_rank  |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |


My efforts so far :



SET @s := 0; 
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs


Edit: (From OP Comments): Ordering is based on the descending order of game_detail




order of game_detail











share|improve this question
























  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    21 hours ago










  • order of game_detail
    – ha͞me̸d̨
    21 hours ago















up vote
8
down vote

favorite
3












I have a table which has 3 fields, I want rank column based on user_id and game_id.



Here is SQL Fiddle :
http://sqlfiddle.com/#!9/883e9d/1



the table already I have :



 user_id | game_id |   game_detial_sum  |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |


expected output :



user_id  | game_id |   game_detial_sum  |  user_game_rank  |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |


My efforts so far :



SET @s := 0; 
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs


Edit: (From OP Comments): Ordering is based on the descending order of game_detail




order of game_detail











share|improve this question
























  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    21 hours ago










  • order of game_detail
    – ha͞me̸d̨
    21 hours ago













up vote
8
down vote

favorite
3









up vote
8
down vote

favorite
3






3





I have a table which has 3 fields, I want rank column based on user_id and game_id.



Here is SQL Fiddle :
http://sqlfiddle.com/#!9/883e9d/1



the table already I have :



 user_id | game_id |   game_detial_sum  |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |


expected output :



user_id  | game_id |   game_detial_sum  |  user_game_rank  |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |


My efforts so far :



SET @s := 0; 
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs


Edit: (From OP Comments): Ordering is based on the descending order of game_detail




order of game_detail











share|improve this question















I have a table which has 3 fields, I want rank column based on user_id and game_id.



Here is SQL Fiddle :
http://sqlfiddle.com/#!9/883e9d/1



the table already I have :



 user_id | game_id |   game_detial_sum  |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |


expected output :



user_id  | game_id |   game_detial_sum  |  user_game_rank  |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |


My efforts so far :



SET @s := 0; 
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs


Edit: (From OP Comments): Ordering is based on the descending order of game_detail




order of game_detail








mysql sql mysql-workbench






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 21 hours ago









Madhur Bhaiya

17.7k62236




17.7k62236










asked 21 hours ago









ha͞me̸d̨

745




745












  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    21 hours ago










  • order of game_detail
    – ha͞me̸d̨
    21 hours ago


















  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    21 hours ago










  • order of game_detail
    – ha͞me̸d̨
    21 hours ago
















Is the ordering based on ascending order of game_id or the descending order of game_detail ?
– Madhur Bhaiya
21 hours ago




Is the ordering based on ascending order of game_id or the descending order of game_detail ?
– Madhur Bhaiya
21 hours ago












order of game_detail
– ha͞me̸d̨
21 hours ago




order of game_detail
– ha͞me̸d̨
21 hours ago












3 Answers
3






active

oldest

votes

















up vote
4
down vote



accepted










In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.



Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.



SET @r := 0, @u := 0; 
SELECT
@r := CASE WHEN @u = dt.user_id THEN @r + 1
ELSE 1
END AS user_game_rank,
@u := dt.user_id AS user_id,
dt.game_detail,
dt.game_id

FROM
( SELECT user_id, game_id, game_detail
FROM game_logs
ORDER BY user_id, game_detail DESC
) AS dt


Result



| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1 | 6 | 260 | 11 |
| 2 | 6 | 100 | 10 |
| 1 | 7 | 1200 | 10 |
| 2 | 7 | 500 | 11 |
| 3 | 7 | 260 | 12 |
| 4 | 7 | 50 | 13 |


View on DB Fiddle





An interesting note from MySQL Docs, which I discovered recently:




Previous releases of MySQL made it possible to assign a value to a
user variable in statements other than SET. This functionality is
supported in MySQL 8.0 for backward compatibility but is subject to
removal in a future release of MySQL.




Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/



General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.



Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:



Schema (MySQL v8.0)



SELECT user_id, 
game_id,
game_detail,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY game_detail DESC) AS user_game_rank
FROM game_logs
ORDER BY user_id, user_game_rank;


Result



| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6 | 11 | 260 | 1 |
| 6 | 10 | 100 | 2 |
| 7 | 10 | 1200 | 1 |
| 7 | 11 | 500 | 2 |
| 7 | 12 | 260 | 3 |
| 7 | 13 | 50 | 4 |


View on DB Fiddle






share|improve this answer






























    up vote
    3
    down vote













    SELECT user_id, game_id, game_detail, 
    CASE WHEN user_id = @lastUserId
    THEN @rank := @rank + 1
    ELSE @rank := 1
    END As user_game_rank,
    @lastUserId := user_id
    FROM game_logs
    cross join (select @rank := 0, @lastUserId := 0) r
    order by user_id, game_detail desc


    SQLFiddle Demo






    share|improve this answer























    • @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
      – Madhur Bhaiya
      12 hours ago










    • @MadhurBhaiya: I added an order by
      – juergen d
      11 hours ago










    • @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
      – Madhur Bhaiya
      11 hours ago










    • @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
      – Madhur Bhaiya
      10 hours ago










    • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
      – Gordon Linoff
      5 hours ago


















    up vote
    0
    down vote













    The best solution in MySQL, prior to version 8.0 is the following:



    select gl.*, 
    (@rn := if(user_id = @lastUserId, @rn + 1,
    if(user_id := @lastUserId, 1, 1)
    )
    ) as user_game_rank,
    @lastUserId := user_id
    from (select gl.*
    from game_logs gl
    order by gl.user_id, gl.game_detail desc
    ) gl cross join
    (select @rn := 0, @lastUserId := 0) params;


    The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).






    share|improve this answer





















      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














       

      draft saved


      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465111%2fset-rank-based-on-multiple-columns%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      4
      down vote



      accepted










      In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.



      Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.



      SET @r := 0, @u := 0; 
      SELECT
      @r := CASE WHEN @u = dt.user_id THEN @r + 1
      ELSE 1
      END AS user_game_rank,
      @u := dt.user_id AS user_id,
      dt.game_detail,
      dt.game_id

      FROM
      ( SELECT user_id, game_id, game_detail
      FROM game_logs
      ORDER BY user_id, game_detail DESC
      ) AS dt


      Result



      | user_game_rank | user_id | game_detail | game_id |
      | -------------- | ------- | ----------- | ------- |
      | 1 | 6 | 260 | 11 |
      | 2 | 6 | 100 | 10 |
      | 1 | 7 | 1200 | 10 |
      | 2 | 7 | 500 | 11 |
      | 3 | 7 | 260 | 12 |
      | 4 | 7 | 50 | 13 |


      View on DB Fiddle





      An interesting note from MySQL Docs, which I discovered recently:




      Previous releases of MySQL made it possible to assign a value to a
      user variable in statements other than SET. This functionality is
      supported in MySQL 8.0 for backward compatibility but is subject to
      removal in a future release of MySQL.




      Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/



      General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.



      Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:



      Schema (MySQL v8.0)



      SELECT user_id, 
      game_id,
      game_detail,
      ROW_NUMBER() OVER (PARTITION BY user_id
      ORDER BY game_detail DESC) AS user_game_rank
      FROM game_logs
      ORDER BY user_id, user_game_rank;


      Result



      | user_id | game_id | game_detail | user_game_rank |
      | ------- | ------- | ----------- | -------------- |
      | 6 | 11 | 260 | 1 |
      | 6 | 10 | 100 | 2 |
      | 7 | 10 | 1200 | 1 |
      | 7 | 11 | 500 | 2 |
      | 7 | 12 | 260 | 3 |
      | 7 | 13 | 50 | 4 |


      View on DB Fiddle






      share|improve this answer



























        up vote
        4
        down vote



        accepted










        In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.



        Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.



        SET @r := 0, @u := 0; 
        SELECT
        @r := CASE WHEN @u = dt.user_id THEN @r + 1
        ELSE 1
        END AS user_game_rank,
        @u := dt.user_id AS user_id,
        dt.game_detail,
        dt.game_id

        FROM
        ( SELECT user_id, game_id, game_detail
        FROM game_logs
        ORDER BY user_id, game_detail DESC
        ) AS dt


        Result



        | user_game_rank | user_id | game_detail | game_id |
        | -------------- | ------- | ----------- | ------- |
        | 1 | 6 | 260 | 11 |
        | 2 | 6 | 100 | 10 |
        | 1 | 7 | 1200 | 10 |
        | 2 | 7 | 500 | 11 |
        | 3 | 7 | 260 | 12 |
        | 4 | 7 | 50 | 13 |


        View on DB Fiddle





        An interesting note from MySQL Docs, which I discovered recently:




        Previous releases of MySQL made it possible to assign a value to a
        user variable in statements other than SET. This functionality is
        supported in MySQL 8.0 for backward compatibility but is subject to
        removal in a future release of MySQL.




        Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/



        General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.



        Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:



        Schema (MySQL v8.0)



        SELECT user_id, 
        game_id,
        game_detail,
        ROW_NUMBER() OVER (PARTITION BY user_id
        ORDER BY game_detail DESC) AS user_game_rank
        FROM game_logs
        ORDER BY user_id, user_game_rank;


        Result



        | user_id | game_id | game_detail | user_game_rank |
        | ------- | ------- | ----------- | -------------- |
        | 6 | 11 | 260 | 1 |
        | 6 | 10 | 100 | 2 |
        | 7 | 10 | 1200 | 1 |
        | 7 | 11 | 500 | 2 |
        | 7 | 12 | 260 | 3 |
        | 7 | 13 | 50 | 4 |


        View on DB Fiddle






        share|improve this answer

























          up vote
          4
          down vote



          accepted







          up vote
          4
          down vote



          accepted






          In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.



          Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.



          SET @r := 0, @u := 0; 
          SELECT
          @r := CASE WHEN @u = dt.user_id THEN @r + 1
          ELSE 1
          END AS user_game_rank,
          @u := dt.user_id AS user_id,
          dt.game_detail,
          dt.game_id

          FROM
          ( SELECT user_id, game_id, game_detail
          FROM game_logs
          ORDER BY user_id, game_detail DESC
          ) AS dt


          Result



          | user_game_rank | user_id | game_detail | game_id |
          | -------------- | ------- | ----------- | ------- |
          | 1 | 6 | 260 | 11 |
          | 2 | 6 | 100 | 10 |
          | 1 | 7 | 1200 | 10 |
          | 2 | 7 | 500 | 11 |
          | 3 | 7 | 260 | 12 |
          | 4 | 7 | 50 | 13 |


          View on DB Fiddle





          An interesting note from MySQL Docs, which I discovered recently:




          Previous releases of MySQL made it possible to assign a value to a
          user variable in statements other than SET. This functionality is
          supported in MySQL 8.0 for backward compatibility but is subject to
          removal in a future release of MySQL.




          Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/



          General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.



          Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:



          Schema (MySQL v8.0)



          SELECT user_id, 
          game_id,
          game_detail,
          ROW_NUMBER() OVER (PARTITION BY user_id
          ORDER BY game_detail DESC) AS user_game_rank
          FROM game_logs
          ORDER BY user_id, user_game_rank;


          Result



          | user_id | game_id | game_detail | user_game_rank |
          | ------- | ------- | ----------- | -------------- |
          | 6 | 11 | 260 | 1 |
          | 6 | 10 | 100 | 2 |
          | 7 | 10 | 1200 | 1 |
          | 7 | 11 | 500 | 2 |
          | 7 | 12 | 260 | 3 |
          | 7 | 13 | 50 | 4 |


          View on DB Fiddle






          share|improve this answer














          In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.



          Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.



          SET @r := 0, @u := 0; 
          SELECT
          @r := CASE WHEN @u = dt.user_id THEN @r + 1
          ELSE 1
          END AS user_game_rank,
          @u := dt.user_id AS user_id,
          dt.game_detail,
          dt.game_id

          FROM
          ( SELECT user_id, game_id, game_detail
          FROM game_logs
          ORDER BY user_id, game_detail DESC
          ) AS dt


          Result



          | user_game_rank | user_id | game_detail | game_id |
          | -------------- | ------- | ----------- | ------- |
          | 1 | 6 | 260 | 11 |
          | 2 | 6 | 100 | 10 |
          | 1 | 7 | 1200 | 10 |
          | 2 | 7 | 500 | 11 |
          | 3 | 7 | 260 | 12 |
          | 4 | 7 | 50 | 13 |


          View on DB Fiddle





          An interesting note from MySQL Docs, which I discovered recently:




          Previous releases of MySQL made it possible to assign a value to a
          user variable in statements other than SET. This functionality is
          supported in MySQL 8.0 for backward compatibility but is subject to
          removal in a future release of MySQL.




          Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/



          General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.



          Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:



          Schema (MySQL v8.0)



          SELECT user_id, 
          game_id,
          game_detail,
          ROW_NUMBER() OVER (PARTITION BY user_id
          ORDER BY game_detail DESC) AS user_game_rank
          FROM game_logs
          ORDER BY user_id, user_game_rank;


          Result



          | user_id | game_id | game_detail | user_game_rank |
          | ------- | ------- | ----------- | -------------- |
          | 6 | 11 | 260 | 1 |
          | 6 | 10 | 100 | 2 |
          | 7 | 10 | 1200 | 1 |
          | 7 | 11 | 500 | 2 |
          | 7 | 12 | 260 | 3 |
          | 7 | 13 | 50 | 4 |


          View on DB Fiddle







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 20 hours ago

























          answered 21 hours ago









          Madhur Bhaiya

          17.7k62236




          17.7k62236
























              up vote
              3
              down vote













              SELECT user_id, game_id, game_detail, 
              CASE WHEN user_id = @lastUserId
              THEN @rank := @rank + 1
              ELSE @rank := 1
              END As user_game_rank,
              @lastUserId := user_id
              FROM game_logs
              cross join (select @rank := 0, @lastUserId := 0) r
              order by user_id, game_detail desc


              SQLFiddle Demo






              share|improve this answer























              • @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
                – Madhur Bhaiya
                12 hours ago










              • @MadhurBhaiya: I added an order by
                – juergen d
                11 hours ago










              • @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
                – Madhur Bhaiya
                11 hours ago










              • @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
                – Madhur Bhaiya
                10 hours ago










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                5 hours ago















              up vote
              3
              down vote













              SELECT user_id, game_id, game_detail, 
              CASE WHEN user_id = @lastUserId
              THEN @rank := @rank + 1
              ELSE @rank := 1
              END As user_game_rank,
              @lastUserId := user_id
              FROM game_logs
              cross join (select @rank := 0, @lastUserId := 0) r
              order by user_id, game_detail desc


              SQLFiddle Demo






              share|improve this answer























              • @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
                – Madhur Bhaiya
                12 hours ago










              • @MadhurBhaiya: I added an order by
                – juergen d
                11 hours ago










              • @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
                – Madhur Bhaiya
                11 hours ago










              • @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
                – Madhur Bhaiya
                10 hours ago










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                5 hours ago













              up vote
              3
              down vote










              up vote
              3
              down vote









              SELECT user_id, game_id, game_detail, 
              CASE WHEN user_id = @lastUserId
              THEN @rank := @rank + 1
              ELSE @rank := 1
              END As user_game_rank,
              @lastUserId := user_id
              FROM game_logs
              cross join (select @rank := 0, @lastUserId := 0) r
              order by user_id, game_detail desc


              SQLFiddle Demo






              share|improve this answer














              SELECT user_id, game_id, game_detail, 
              CASE WHEN user_id = @lastUserId
              THEN @rank := @rank + 1
              ELSE @rank := 1
              END As user_game_rank,
              @lastUserId := user_id
              FROM game_logs
              cross join (select @rank := 0, @lastUserId := 0) r
              order by user_id, game_detail desc


              SQLFiddle Demo







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited 11 hours ago

























              answered 21 hours ago









              juergen d

              157k24198254




              157k24198254












              • @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
                – Madhur Bhaiya
                12 hours ago










              • @MadhurBhaiya: I added an order by
                – juergen d
                11 hours ago










              • @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
                – Madhur Bhaiya
                11 hours ago










              • @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
                – Madhur Bhaiya
                10 hours ago










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                5 hours ago


















              • @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
                – Madhur Bhaiya
                12 hours ago










              • @MadhurBhaiya: I added an order by
                – juergen d
                11 hours ago










              • @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
                – Madhur Bhaiya
                11 hours ago










              • @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
                – Madhur Bhaiya
                10 hours ago










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                5 hours ago
















              @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
              – Madhur Bhaiya
              12 hours ago




              @GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased
              – Madhur Bhaiya
              12 hours ago












              @MadhurBhaiya: I added an order by
              – juergen d
              11 hours ago




              @MadhurBhaiya: I added an order by
              – juergen d
              11 hours ago












              @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
              – Madhur Bhaiya
              11 hours ago




              @juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).
              – Madhur Bhaiya
              11 hours ago












              @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
              – Madhur Bhaiya
              10 hours ago




              @juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…
              – Madhur Bhaiya
              10 hours ago












              @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
              – Gordon Linoff
              5 hours ago




              @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
              – Gordon Linoff
              5 hours ago










              up vote
              0
              down vote













              The best solution in MySQL, prior to version 8.0 is the following:



              select gl.*, 
              (@rn := if(user_id = @lastUserId, @rn + 1,
              if(user_id := @lastUserId, 1, 1)
              )
              ) as user_game_rank,
              @lastUserId := user_id
              from (select gl.*
              from game_logs gl
              order by gl.user_id, gl.game_detail desc
              ) gl cross join
              (select @rn := 0, @lastUserId := 0) params;


              The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).






              share|improve this answer

























                up vote
                0
                down vote













                The best solution in MySQL, prior to version 8.0 is the following:



                select gl.*, 
                (@rn := if(user_id = @lastUserId, @rn + 1,
                if(user_id := @lastUserId, 1, 1)
                )
                ) as user_game_rank,
                @lastUserId := user_id
                from (select gl.*
                from game_logs gl
                order by gl.user_id, gl.game_detail desc
                ) gl cross join
                (select @rn := 0, @lastUserId := 0) params;


                The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  The best solution in MySQL, prior to version 8.0 is the following:



                  select gl.*, 
                  (@rn := if(user_id = @lastUserId, @rn + 1,
                  if(user_id := @lastUserId, 1, 1)
                  )
                  ) as user_game_rank,
                  @lastUserId := user_id
                  from (select gl.*
                  from game_logs gl
                  order by gl.user_id, gl.game_detail desc
                  ) gl cross join
                  (select @rn := 0, @lastUserId := 0) params;


                  The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).






                  share|improve this answer












                  The best solution in MySQL, prior to version 8.0 is the following:



                  select gl.*, 
                  (@rn := if(user_id = @lastUserId, @rn + 1,
                  if(user_id := @lastUserId, 1, 1)
                  )
                  ) as user_game_rank,
                  @lastUserId := user_id
                  from (select gl.*
                  from game_logs gl
                  order by gl.user_id, gl.game_detail desc
                  ) gl cross join
                  (select @rn := 0, @lastUserId := 0) params;


                  The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 5 hours ago









                  Gordon Linoff

                  746k32285390




                  746k32285390






























                       

                      draft saved


                      draft discarded



















































                       


                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465111%2fset-rank-based-on-multiple-columns%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      數位音樂下載

                      When can things happen in Etherscan, such as the picture below?

                      格利澤436b