MySQL - Select query with first rows where a field value > 10 without ordering by this field

Question

I would like to make a SELECT in my player table where, in the result, the first rows are the players who played more than 10 games. What is preventing me from just doing an ORDER BY total_games is that I would like to ORDER by signin_year and then by player_type for the following result.

+--------------+-------------+--------------+--------------+
| username     | signin_year | player_type  | total_games  |
+--------------+-------------+--------------+--------------+
| John Doe     | 2019        | 10           | 62           |
| Jon Skeet    | 2019        | 20           | 2479         |
| Shmebulock   | 2020        | 10           | 82           |
| Norman       | 2020        | 20           | 154          |
| Bob          | 2019        | 10           | 3            |
| Alice        | 2020        | 10           | 9            |
| Community    | 2020        | 20           | -1           |
+--------------+-------------+--------------+--------------+

I tried a bunch of WHEREs and ORDER BYs but I can't seems to make it work, is this even possible or will I need to use a subquery ?

Answer 1

Try using ORDER BY along with a CASE expression:

SELECT *
FROM player
ORDER BY
    CASE WHEN total_games > 10 THEN 0 ELSE 1 END,
    signin_year,
    player_type;

This places players having played more than 10 total games first, followed by all other players. Within these two blocks, the data is sorted ascending by signin year followed by ascending player type.