craigb88 craigb88 - 6 months ago 17
SQL Question

MySQL order by asc ( If priority field 1 move to top)

this is my first post and I'm pretty new to MySql so apologies if it's a simple question. I have found a couple of answer but could get them to work so figured I post and hope someone could explain things a little clearer.

So I have a tabled called players which has


player_id, player_name, player_morning_score, player_afternoon_score, player_priority


I do a query to add the morning score and afternoon score together then display the asc. This all works. What I need to do is if 2 players have a score of 21 i need to be able to add a priority of 1 to one of the players to move them above the other player.

Heres my code.

SELECT player_id, player_name, (player_morning_score + player_afternoon_score) AS total
FROM players
ORDER BY total, ASC


My Results are displayed like this

Craig 10
Ben 15
Jamie 15
Steve 7


So basically I need to add an if stament to check if jamie or ben has the priority of 1 to determin which is above the other.

There might be another way to query it but the two players with 15 the one who needs to sit at the top is the one with the lowest score on the afternoon score.

Hope this is enough information. Thanks in advance.

Answer

You have situation when two or more player have same score.

At that time you should check total with priority of player as you have this fields. And consider afternoon score as desc so you get latest player.

SELECT player_id, player_name, (player_morning_score + player_afternoon_score) AS total
FROM players
ORDER BY total,player_priority ASC, player_afternoon_score DESC