Determine Rank based on Multiple Columns in MySQL











up vote
7
down vote

favorite
3












I have a table which has 3 fields, I want to 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
    Nov 25 at 6:15










  • order of game_detail
    – ha͞me̸d̨
    Nov 25 at 6:17















up vote
7
down vote

favorite
3












I have a table which has 3 fields, I want to 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
    Nov 25 at 6:15










  • order of game_detail
    – ha͞me̸d̨
    Nov 25 at 6:17













up vote
7
down vote

favorite
3









up vote
7
down vote

favorite
3






3





I have a table which has 3 fields, I want to 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 to 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 sql-rank






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 27 at 15:11









Salman A

173k66331419




173k66331419










asked Nov 25 at 6:11









ha͞me̸d̨

695




695












  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    Nov 25 at 6:15










  • order of game_detail
    – ha͞me̸d̨
    Nov 25 at 6:17


















  • Is the ordering based on ascending order of game_id or the descending order of game_detail ?
    – Madhur Bhaiya
    Nov 25 at 6:15










  • order of game_detail
    – ha͞me̸d̨
    Nov 25 at 6:17
















Is the ordering based on ascending order of game_id or the descending order of game_detail ?
– Madhur Bhaiya
Nov 25 at 6:15




Is the ordering based on ascending order of game_id or the descending order of game_detail ?
– Madhur Bhaiya
Nov 25 at 6:15












order of game_detail
– ha͞me̸d̨
Nov 25 at 6:17




order of game_detail
– ha͞me̸d̨
Nov 25 at 6:17












4 Answers
4






active

oldest

votes

















up vote
5
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.



Edit: Based on MySQL docs and @Gordon Linoff's observation:




The order of evaluation for expressions involving user variables is
undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
evaluates @a first and then performs the assignment.




We will need to evaluate row number and assign the user_id value to @u variable within the same expression.



SET @r := 0, @u := 0; 
SELECT
@r := CASE WHEN @u = dt.user_id
THEN @r + 1
WHEN @u := dt.user_id /* Notice := instead of = */
THEN 1
END AS user_game_rank,
dt.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













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



    select gl.*, 
    (@rn := if(@lastUserId = user_id, @rn + 1,
    if(@lastUserId := user_id, 1, 1)
    )
    ) as user_game_rank
    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























    • @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
      – Madhur Bhaiya
      Nov 26 at 12:08






    • 1




      @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
      – Gordon Linoff
      Nov 26 at 12:31










    • Makes sense now. +1
      – Madhur Bhaiya
      Nov 26 at 12:31


















    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
      Nov 25 at 14:55










    • @MadhurBhaiya: I added an order by
      – juergen d
      Nov 25 at 16:30










    • @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
      Nov 25 at 16:32










    • @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
      Nov 25 at 16:35










    • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
      – Gordon Linoff
      Nov 25 at 21:45


















    up vote
    1
    down vote













    You can use a very simple correlated sub query:



    SELECT *, (
    SELECT COUNT(DISTINCT game_detail) + 1
    FROM game_logs AS x
    WHERE user_id = t.user_id AND game_detail > t.game_detail
    ) AS user_game_rank
    FROM game_logs AS t
    ORDER BY user_id, user_game_rank


    DB Fiddle



    It is slower but far more reliable than user variables. All it takes is one JOIN to break them.






    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%2fdetermine-rank-based-on-multiple-columns-in-mysql%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      4 Answers
      4






      active

      oldest

      votes








      4 Answers
      4






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      5
      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.



      Edit: Based on MySQL docs and @Gordon Linoff's observation:




      The order of evaluation for expressions involving user variables is
      undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
      evaluates @a first and then performs the assignment.




      We will need to evaluate row number and assign the user_id value to @u variable within the same expression.



      SET @r := 0, @u := 0; 
      SELECT
      @r := CASE WHEN @u = dt.user_id
      THEN @r + 1
      WHEN @u := dt.user_id /* Notice := instead of = */
      THEN 1
      END AS user_game_rank,
      dt.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
        5
        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.



        Edit: Based on MySQL docs and @Gordon Linoff's observation:




        The order of evaluation for expressions involving user variables is
        undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
        evaluates @a first and then performs the assignment.




        We will need to evaluate row number and assign the user_id value to @u variable within the same expression.



        SET @r := 0, @u := 0; 
        SELECT
        @r := CASE WHEN @u = dt.user_id
        THEN @r + 1
        WHEN @u := dt.user_id /* Notice := instead of = */
        THEN 1
        END AS user_game_rank,
        dt.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
          5
          down vote



          accepted







          up vote
          5
          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.



          Edit: Based on MySQL docs and @Gordon Linoff's observation:




          The order of evaluation for expressions involving user variables is
          undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
          evaluates @a first and then performs the assignment.




          We will need to evaluate row number and assign the user_id value to @u variable within the same expression.



          SET @r := 0, @u := 0; 
          SELECT
          @r := CASE WHEN @u = dt.user_id
          THEN @r + 1
          WHEN @u := dt.user_id /* Notice := instead of = */
          THEN 1
          END AS user_game_rank,
          dt.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.



          Edit: Based on MySQL docs and @Gordon Linoff's observation:




          The order of evaluation for expressions involving user variables is
          undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
          evaluates @a first and then performs the assignment.




          We will need to evaluate row number and assign the user_id value to @u variable within the same expression.



          SET @r := 0, @u := 0; 
          SELECT
          @r := CASE WHEN @u = dt.user_id
          THEN @r + 1
          WHEN @u := dt.user_id /* Notice := instead of = */
          THEN 1
          END AS user_game_rank,
          dt.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 Nov 26 at 12:50

























          answered Nov 25 at 6:17









          Madhur Bhaiya

          18.8k62236




          18.8k62236
























              up vote
              4
              down vote













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



              select gl.*, 
              (@rn := if(@lastUserId = user_id, @rn + 1,
              if(@lastUserId := user_id, 1, 1)
              )
              ) as user_game_rank
              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























              • @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
                – Madhur Bhaiya
                Nov 26 at 12:08






              • 1




                @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
                – Gordon Linoff
                Nov 26 at 12:31










              • Makes sense now. +1
                – Madhur Bhaiya
                Nov 26 at 12:31















              up vote
              4
              down vote













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



              select gl.*, 
              (@rn := if(@lastUserId = user_id, @rn + 1,
              if(@lastUserId := user_id, 1, 1)
              )
              ) as user_game_rank
              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























              • @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
                – Madhur Bhaiya
                Nov 26 at 12:08






              • 1




                @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
                – Gordon Linoff
                Nov 26 at 12:31










              • Makes sense now. +1
                – Madhur Bhaiya
                Nov 26 at 12:31













              up vote
              4
              down vote










              up vote
              4
              down vote









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



              select gl.*, 
              (@rn := if(@lastUserId = user_id, @rn + 1,
              if(@lastUserId := user_id, 1, 1)
              )
              ) as user_game_rank
              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(@lastUserId = user_id, @rn + 1,
              if(@lastUserId := user_id, 1, 1)
              )
              ) as user_game_rank
              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








              edited Nov 26 at 12:30

























              answered Nov 25 at 21:46









              Gordon Linoff

              749k34285391




              749k34285391












              • @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
                – Madhur Bhaiya
                Nov 26 at 12:08






              • 1




                @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
                – Gordon Linoff
                Nov 26 at 12:31










              • Makes sense now. +1
                – Madhur Bhaiya
                Nov 26 at 12:31


















              • @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
                – Madhur Bhaiya
                Nov 26 at 12:08






              • 1




                @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
                – Gordon Linoff
                Nov 26 at 12:31










              • Makes sense now. +1
                – Madhur Bhaiya
                Nov 26 at 12:31
















              @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
              – Madhur Bhaiya
              Nov 26 at 12:08




              @lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.
              – Madhur Bhaiya
              Nov 26 at 12:08




              1




              1




              @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
              – Gordon Linoff
              Nov 26 at 12:31




              @MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.
              – Gordon Linoff
              Nov 26 at 12:31












              Makes sense now. +1
              – Madhur Bhaiya
              Nov 26 at 12:31




              Makes sense now. +1
              – Madhur Bhaiya
              Nov 26 at 12:31










              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
                Nov 25 at 14:55










              • @MadhurBhaiya: I added an order by
                – juergen d
                Nov 25 at 16:30










              • @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
                Nov 25 at 16:32










              • @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
                Nov 25 at 16:35










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                Nov 25 at 21:45















              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
                Nov 25 at 14:55










              • @MadhurBhaiya: I added an order by
                – juergen d
                Nov 25 at 16:30










              • @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
                Nov 25 at 16:32










              • @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
                Nov 25 at 16:35










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                Nov 25 at 21:45













              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 Nov 25 at 16:30

























              answered Nov 25 at 6:16









              juergen d

              158k24198256




              158k24198256












              • @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
                Nov 25 at 14:55










              • @MadhurBhaiya: I added an order by
                – juergen d
                Nov 25 at 16:30










              • @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
                Nov 25 at 16:32










              • @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
                Nov 25 at 16:35










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                Nov 25 at 21:45


















              • @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
                Nov 25 at 14:55










              • @MadhurBhaiya: I added an order by
                – juergen d
                Nov 25 at 16:30










              • @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
                Nov 25 at 16:32










              • @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
                Nov 25 at 16:35










              • @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
                – Gordon Linoff
                Nov 25 at 21:45
















              @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
              Nov 25 at 14:55




              @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
              Nov 25 at 14:55












              @MadhurBhaiya: I added an order by
              – juergen d
              Nov 25 at 16:30




              @MadhurBhaiya: I added an order by
              – juergen d
              Nov 25 at 16:30












              @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
              Nov 25 at 16:32




              @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
              Nov 25 at 16:32












              @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
              Nov 25 at 16:35




              @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
              Nov 25 at 16:35












              @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
              – Gordon Linoff
              Nov 25 at 21:45




              @MadhurBhaiya . . . You're right. This makes the same mistake yours does.
              – Gordon Linoff
              Nov 25 at 21:45










              up vote
              1
              down vote













              You can use a very simple correlated sub query:



              SELECT *, (
              SELECT COUNT(DISTINCT game_detail) + 1
              FROM game_logs AS x
              WHERE user_id = t.user_id AND game_detail > t.game_detail
              ) AS user_game_rank
              FROM game_logs AS t
              ORDER BY user_id, user_game_rank


              DB Fiddle



              It is slower but far more reliable than user variables. All it takes is one JOIN to break them.






              share|improve this answer



























                up vote
                1
                down vote













                You can use a very simple correlated sub query:



                SELECT *, (
                SELECT COUNT(DISTINCT game_detail) + 1
                FROM game_logs AS x
                WHERE user_id = t.user_id AND game_detail > t.game_detail
                ) AS user_game_rank
                FROM game_logs AS t
                ORDER BY user_id, user_game_rank


                DB Fiddle



                It is slower but far more reliable than user variables. All it takes is one JOIN to break them.






                share|improve this answer

























                  up vote
                  1
                  down vote










                  up vote
                  1
                  down vote









                  You can use a very simple correlated sub query:



                  SELECT *, (
                  SELECT COUNT(DISTINCT game_detail) + 1
                  FROM game_logs AS x
                  WHERE user_id = t.user_id AND game_detail > t.game_detail
                  ) AS user_game_rank
                  FROM game_logs AS t
                  ORDER BY user_id, user_game_rank


                  DB Fiddle



                  It is slower but far more reliable than user variables. All it takes is one JOIN to break them.






                  share|improve this answer














                  You can use a very simple correlated sub query:



                  SELECT *, (
                  SELECT COUNT(DISTINCT game_detail) + 1
                  FROM game_logs AS x
                  WHERE user_id = t.user_id AND game_detail > t.game_detail
                  ) AS user_game_rank
                  FROM game_logs AS t
                  ORDER BY user_id, user_game_rank


                  DB Fiddle



                  It is slower but far more reliable than user variables. All it takes is one JOIN to break them.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 27 at 6:10

























                  answered Nov 27 at 5:58









                  Salman A

                  173k66331419




                  173k66331419






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465111%2fdetermine-rank-based-on-multiple-columns-in-mysql%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

                      Ellipse (mathématiques)

                      Quarter-circle Tiles

                      Mont Emei