silver silver - 5 months ago 10
MySQL Question

How to conditional order results in mySql?

I have this scenario:

A user enter my application and push a button to look for a pair on a game. When he click the button, I do a insert in a table, whit his ID, his level, his countryID and a datetime whith NOW().
Then, I need to look in this table, trying to find a pair to play with him.

my problem is, i need to order the results based on the user level.

So, imagine my table have this rows:


|ID |userID|userLv| date_add | country_ID |
----------------------------------------------------
| 1 | 1 |3 |2016-06-24 12:09:06 | 5 |
| 2 | 2 |2 |2016-06-24 04:26:24 | 8 |
| 3 | 3 |2 |2016-06-24 11:38:54 | 1 |
| 4 | 4 |1 |2016-06-24 11:10:06 | 9 |
| 5 | 5 |2 |2016-06-24 11:35:49 | 6 |
| 6 | 6 |1 |2016-06-24 20:09:13 | 10 |


If a user with level 1 click the button, he needs to be matched with another user of level 1, odered by date_add ASC. But if there are no users of level 1 available, he will be paired with a user of level 2, again order by date_add ASC. if there are no level 2 users available, the he will be paired with a user of level 3.

Basically, i need a script that can return the rows ordered first by the userLv = x, then the other levels. How to do this with only one request to the database? Does this even make sense?

Answer

You can simple use a correlated query with LIMIT to get the second user id :

SELECT t.*,
       (SELECT s.userID FROM YourTable s
        WHERE s.userLv >= t.userLv
        ORDER BY s.userLv 
        LIMIT 1) as user2_id
FROM YourTable t
WHERE t.userID = <YourParamOrWhatEver>