MySQL - How to determine position ranking of table sorted by two columns and allowing for ties?

Question

I have a table (tournament) that contains a list of teams and their accumulated points and goal difference (goals scored - goals conceded).

Tournament

+-----------+--------+-----------+
|   team    | points | goal_diff |
+-----------+--------+-----------+
| USA       |      7 |   -2      |
| Brazil    |     12 |   +7      |
| Argentina |     12 |   +10     |
| Germany   |      7 |   -2      |
| Italy     |      3 |    0      |
+-----------+--------+-----------+

How do I rank them by points, and then by goal difference keeping ties with the same ranking and skipping the subsequent ranking position? I'm looking for the query to yield this end result:

+-----------+--------+-----------+------+
|   team    | points | goal_diff | rank |
+-----------+--------+-----------+------+
| Argentina |     12 | +10       |    1 |
| Brazil    |     12 | +7        |    2 |
| USA       |      7 | -2        |    3 |
| Germany   |      7 | -2        |    3 |
| Italy     |      3 | 0         |    5 |
+-----------+--------+-----------+------+

I have been able to rank them and keep ties the same by the points column, but have no idea what to do to include the second column condition

SELECT s.team, s.points, s.goal_diff,
    (( SELECT COUNT(DISTINCT points) FROM Tournament WHERE points > s.points ) + 1) AS rank
FROM Tournament s
ORDER BY s.points DESC

Thanks for the help!

Answer 1

I missed : in #1(after @rank), that is the reason why show you blob and null value

SELECT
    a.team,
    a.points,
    a.goal_diff,
    a.rank 
FROM
    (
    SELECT
        t.team,
        t.points,
        t.goal_diff,
    IF
        ( @points = t.points, 
        IF 
            (@goal_diff = t.goal_diff, @rank, @rank :=@inRank),
        @rank := @inRank ) AS rank,                               # 2
        @inRank := @inRank + 1,                                   # 3
        @points := t.points,                                      # 4
        @goal_diff := t.goal_diff  
    FROM
        `tournament` t,
        ( SELECT @points := NULL, @goal_diff := NULL, @rank := 0, @inRank := 1 ) b  # 1
    ORDER BY
    t.points DESC, t.goal_diff DESC
    ) a

Explanation:

@ is used to declare a variable. := means assign value to a variable. #1 sql is actually a variable initial.

@points :  a custom declared variable for storing the points value in last row
@rank   :  a custom declared variable for storing regular condition rank number  
@inRank :  a custom declared variable for storing a count of row

if(boolean, trueResult, falseResult) function in mysql is kind of like a ternary operation. In #2, if the initial variable @points is equals to the value selected, it would show the value of @Rank which is 0 now. This if() function is used to judge if points values are the same.

After the if() function, we assign the variables in #3 and #4.

sql #3 increases in every row, this variable would count the row number. Thus, I can get the rank when points are not same. I think renaming @inRank to @increaseRank would be better.

sql #4 assigns the points value of this row to the variable. We use it to campare with the points value of next row.

Answer 2

So, basically, this is a minor edit from @wl.GIG answer. The result I get when running @wl.GIG is as below;

+-------------+----------+-------------+--------+
|    team     |  points  |  goal_diff  |  rank  |
+-------------+----------+-------------+--------+
|  Brazil     |      12  |          7  |     1  |
|  Argentina  |      12  |         10  |     1  |
|  USA        |       7  |         -2  |     3  |
|  Germany    |       7  |         -2  |     3  |
|  Italy      |       3  |          0  |     5  |
+-------------+----------+-------------+--------+ 

It's not giving the correct rank as OP's required but it's a very good approach and I'm very interested in it. So, I've made tests that can return the rank as per OP's requirement and here is what I come up with:

SELECT
    a.team,
    a.points,
    a.goal_diff,
    a.rank 
FROM (SELECT
        t.team,
        t.points,
        t.points+t.goal_diff AS tp, -- I've added this.
        t.goal_diff,
     IF
        (@points = t.points+t.goal_diff, --- changed this part.
        @rank, @rank := @inRank) AS rank,
        @inRank := @inRank + 1,
        @Points := t.points+t.goal_diff --- and changed this part.
    FROM
        `tournament` t,
        ( SELECT @points := NULL, @rank = 0, @inRank := 1 ) b 
        ORDER BY t.points DESC,t.goal_diff DESC 
        -- and added another condition in the ordering
        ) a;

As you can see, it wasn't a big edit but I with this, I get the result like below:

+-------------+----------+-------------+--------+
|    team     |  points  |  goal_diff  |  rank  |
+-------------+----------+-------------+--------+
|  Argentina  |      12  |         10  |     1  |
|  Brazil     |      12  |          7  |     2  |
|  USA        |       7  |         -2  |     3  |
|  Germany    |       7  |         -2  |     3  |
|  Italy      |       3  |          0  |     5  |
+-------------+----------+-------------+--------+

That's it.

Answer 3

For anyone trying to figure this out, I ended up upgrading my DB to MariaDB 10.3.14 and used the RANK() function which does exactly what I needed here.

Here's the code:

SELECT team, points, goal_diff,
    RANK() OVER (
        ORDER BY
            points DESC,
            goal_diff DESC
        ) rank
FROM Tournament;

I understand upgrading the DB may not be an option for everyone, but it was a very easy solution in my case. Hope it is also helpful for others.