Question 112846 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Chess results to leader board

I’ve got a table containing results of chess matches:

id player_white player_black result session_start session_end
1 Jonathan1 TrentX 0 2020-01-01 13:10:10 2020-01-01 13:15:23
2 TrentX Jonathan1 1 2020-01-01 13:18:32 2020-01-01 13:23:13
3 Ezekiel2001 Jonathan1 1 2020-01-01 13:30:12 2020-01-01 13:37:01
4 Ezekiel2001 TrentX 3 2020-01-01 13:40:08 2020-01-01 13:44:02
5 Jonathan1 Ezekiel2001 4 2020-01-01 13:48:32 2020-01-01 13:53:56
6 TrentX Ezekiel2001 1 2020-01-01 13:56:30 2020-01-01 13:59:02

result is an enum where:

  • 0 = white victory
  • 1 = black victory
  • 2 = draw by agreement
  • 3 = draw by stalemate
  • 4 = draw by repetition
  • 5 = draw by fifty move rule
  • 6 = aborted

I would like to sort players by maximum number of victories and draws to display a leader board. Is there any straightforward way to get this leader board view or are any changes required in the way the data is organized?

Tags:

2

Answers


  1. 0

    You can list the white and black victories along with draws and then apply group by on counts to get the result set.
    Please refer below.

    SELECT player, count(1) number_of_victories_and_draws
FROM(
SELECT player_white player, result from plays pw_victory_draw_table where result IN( 0, 2, 3, 4, 5)
UNION ALL
SELECT player_black player, result  from plays pb_victory_draw_table where result IN (1, 2, 3, 4, 5)
) playes_union_table
GROUP BY player
ORDER BY 1;

    Result:

    Ezekiel2001 3
Jonathan1   4
TrentX  1
    Login or Signup to reply.
  2. 0

    "… changes required in the way the data is organized?"

    Lacking normalization every condition requires sub-query :

    SELECT
                        player_name,
    sum(player_wins) AS player_wins,
    sum(player_loss) AS player_loss,
    sum(player_draw) AS player_draw
FROM (

    SELECT
        player_white AS player_name,
        count(*)     AS player_wins,
        0            AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 0

    UNION ALL

    SELECT
        player_black AS player_name,
        count(*)     AS player_wins,
        0            AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 1

    UNION ALL

    SELECT
        player_white AS player_name,
        0            AS player_wins,
        count(*)     AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 1

    UNION ALL

    SELECT
        player_black AS player_name,
        0            AS player_wins,
        count(*)     AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 0

    UNION ALL

    SELECT
        player_white AS player_name,
        0            AS player_wins,
        0            AS player_loss,
        count(*)     AS player_draw
    FROM
        table_name
    WHERE
        result BETWEEN 2 AND 5

    UNION ALL

    SELECT
        player_black AS player_name,
        0            AS player_wins,
        0            AS player_loss,
        count(*)     AS player_draw
    FROM
        table_name
    WHERE
        result BETWEEN 2 AND 5

)
GROUP BY
    player_name
ORDER BY
    player_wins DESC,
    player_loss  ASC,
    player_draw DESC,
    player_name  ASC
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search