MySQL SELECT and ORDER BY

Question

In relation to: MySQL ORDER BY or GROUP BY, I do it more complicated. I have an id_competitor with various scores in different routes.

id_competitor   score    id_route
1                WIN        2
2                WIN        2
3                50+        2
4                50+        2
1                70         1
2                70+        1
3                70         1
4                WIN        1

Here is the table "route"

id       name
1      semi-final
2        final

The result should be in the following order:

id_competitor
2
1
4
3

Because they equalized in the final and to break a deadlock we look the semi-final

Answer 1

Consider the following data set...

DROP TABLE IF EXISTS results;

CREATE TABLE results
(id_competitor   INT NOT NULL
,score    INT NOT NULL
,id_route INT NOT NULL
,PRIMARY KEY(id_competitor,id_route)
);

INSERT INTO results VALUES
(1,100,2),
(2,100,2),
(3,60,2),
(4 ,60,2),
(1,70,1),
(2,80,1),
(3,70,1),
(4,100,1);

SELECT * FROM results;
+---------------+-------+----------+
| id_competitor | score | id_route |
+---------------+-------+----------+
|             1 |    70 |        1 |
|             1 |   100 |        2 |
|             2 |    80 |        1 |
|             2 |   100 |        2 |
|             3 |    70 |        1 |
|             3 |    60 |        2 |
|             4 |   100 |        1 |
|             4 |    60 |        2 |
+---------------+-------+----------+

We want to arrange competitors by highest score on id_route=2. Where scores are tied, results from id_route 1 should be taken into account. The correct order should be 2,1,4,3.

Intermediate solution...

SELECT * 
  FROM results x 
  JOIN results y 
    ON y.id_competitor = x.id_competitor 
   AND y.id_route = 1 
 WHERE x.id_route = 2;
+---------------+-------+----------+---------------+-------+----------+
| id_competitor | score | id_route | id_competitor | score | id_route |
+---------------+-------+----------+---------------+-------+----------+
|             1 |   100 |        2 |             1 |    70 |        1 |
|             2 |   100 |        2 |             2 |    80 |        1 |
|             3 |    60 |        2 |             3 |    70 |        1 |
|             4 |    60 |        2 |             4 |   100 |        1 |
+---------------+-------+----------+---------------+-------+----------+

Complete solution...

SELECT x.id_competitor
     , x.score final_score
     , y.score semi_final_score 
  FROM results x 
  JOIN results y 
    ON y.id_competitor = x.id_competitor 
   AND y.id_route = 1 
 WHERE x.id_route = 2 
 ORDER 
    BY final_score DESC
     , semi_final_score DESC;
+---------------+-------------+------------------+
| id_competitor | final_score | semi_final_score |
+---------------+-------------+------------------+
|             2 |         100 |               80 |
|             1 |         100 |               70 |
|             4 |          60 |              100 |
|             3 |          60 |               70 |
+---------------+-------------+------------------+