Mysql Ranking on on Left JOIN

Question

I have two tables (teams and matches) and I want calculate the game table using points and #rank position.

The tables are very simple: Teams and matches (battles). The last column in 'battle' means if the battle was played or not.

DROP TABLE IF EXISTS monsters;
DROP TABLE IF EXISTS battles;

CREATE TABLE monsters (id_monster int auto_increment primary key, monster varchar(50));
CREATE TABLE battles  (id_battle int auto_increment primary key, monster_h int, monster_a int, score_a int, score_h int, played tinyint);

Here some fake data (Edit: I've updated the script removing random score and apply fixed scored.)

INSERT INTO monsters (monster) VALUES ("Nembo"), ("Kid"), ("Captain"), ("OOI"), ("Koky"), ("Rudes");

INSERT INTO battles (monster_h, monster_a, score_h, score_a, played) VALUES

    (1,2, 3, 2,1),
(2,1, 2, 2,1),
(3,4, 1, 0,1),
(4,3, 0, 1,1),
(5,6, 0, 0,0),
(6,5, 5, 2,1),
(1,3, 6, 0,1),
(3,1, 2, 7,1),
(2,5, 1, 1,1),
(6,4, 0, 0,0),
(4,6, 0, 0,0);

Here my question

As you can see the Points are calculated correctly and table is good! But... #rank I wrong! I think I should join it too. Can you help me on #rank ?

SELECT m.monster,
        (SUM(CASE WHEN 
                (b.monster_h = m.id_monster AND b.score_h > b.score_a) OR 
                (b.monster_a = m.id_monster AND b.score_a > b.score_h) THEN 3 ELSE 0 END) +

                SUM(CASE WHEN
                (b.monster_h = m.id_monster  OR b.monster_a = m.id_monster) AND b.score_a = b.score_h THEN 1 ELSE 0 END)

                ) as pt,

               (SUM(CASE WHEN 
                (b.monster_h= m.id_monster OR b.monster_a= m.id_monster) THEN 1 ELSE 0 END) ) as pl,

                (@rank := @rank + 1) AS rank 

FROM   ( monsters as m )
LEFT JOIN battles as b ON m.id_monster IN (b.monster_a,b.monster_h)
,( SELECT @rank := 0 ) AS vars 

where b.played = 1

GROUP BY m.monster

order by pt DESC, rand();

The result: with a wrong #rank

    Monster, PTs, BATTLES, #RANK (good rank should be)

    Nembo,    10,  4,    1       #1
    Captain,  6,   4,    3       #2
    Rudes,    3,   1,    6       #3
    Kid,      2,   3,    2       #4
    Koky,     1,   2,    5       #5
    OOI,      0,   2,    4       #6

UPDATE

Use this data to have more monsters with same points and scores:

(1,2, 3, 2,1),
(2,1, 2, 2,1),
(3,4, 3, 0,1),
(4,3, 0, 1,1),
(5,6, 0, 0,0),
(6,5, 16, 12,1),
(1,3, 6, 0,1),
(3,1, 2, 7,1),
(2,5, 1,1,1),
(6,4, 0, 0,0),
(4,6, 0, 1,1),
(4,2,1,0,1),
(4,5,5,0,1),
(2,3,2,0,1),
(2,1,1,1,1);

and then use this script as Stefan said:

SELECT *,  (@rank := @rank + 1) AS rank 
FROM ( SELECT @rank := 0 ) as r ,
(SELECT m.monster,
    (SUM(CASE WHEN 
            (b.monster_h = m.id_monster AND b.score_h > b.score_a) OR 
            (b.monster_a = m.id_monster AND b.score_a > b.score_h) THEN 3 ELSE 0 END) +
    SUM(CASE WHEN
        (b.monster_h = m.id_monster  OR b.monster_a = m.id_monster) AND
        b.score_a = b.score_h THEN 1 ELSE 0 END)) as pt,

    (SUM(CASE WHEN 
        (b.monster_h= m.id_monster OR b.monster_a= m.id_monster) THEN 1 ELSE 0 END)) as pl,

 (SUM(CASE WHEN 
        (b.monster_h= m.id_monster ) THEN b.score_h ELSE 0 END) + 

SUM(CASE WHEN 
        (b.monster_a= m.id_monster ) THEN b.score_a ELSE 0 END))  as scored

, floor(rand()*1000) as coin

FROM monsters as m
LEFT JOIN battles as b ON m.id_monster IN (b.monster_a,b.monster_h)
where b.played = 1
GROUP BY m.monster) as result
order by result.pt DESC, scored DESC , coin DESC

The table is ordered by POINTS as first criteria, then scored. And it works. But if I use the "coin" as 3th criteria.... it doesn't works!

Answer 1

Great that you included all scripts to recreate your situation.

Put your results from your current query into a subquery, the trick with the @rank doesn't work with GROUP BY.

With this script I get the correct results:

SELECT *,  (@rank := @rank + 1) AS rank 
FROM ( SELECT @rank := 0 ) as r ,
(SELECT m.monster,
    (SUM(CASE WHEN 
            (b.monster_h = m.id_monster AND b.score_h > b.score_a) OR 
            (b.monster_a = m.id_monster AND b.score_a > b.score_h) THEN 3 ELSE 0 END) +
    SUM(CASE WHEN
        (b.monster_h = m.id_monster  OR b.monster_a = m.id_monster) AND
        b.score_a = b.score_h THEN 1 ELSE 0 END)) as pt,
    (SUM(CASE WHEN 
        (b.monster_h= m.id_monster OR b.monster_a= m.id_monster) THEN 1 ELSE 0 END)) as pl
FROM monsters as m
LEFT JOIN battles as b ON m.id_monster IN (b.monster_a,b.monster_h)
where b.played = 1
GROUP BY m.monster) as result
order by result.pt DESC