CODEWITHSUNDEEP
phpmysql
CIB
CIB

Reputation: 785

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.

Upvotes: 2

Views: 125

Answers (3)

Bhadresh Kathiriya
Bhadresh Kathiriya

Reputation: 3244

you are see asc order:

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

or desc order:

SELECT player_id, player_name, (player_morning_score + player_afternoon_score) AS total

FROM players

ORDER BY total desc

Upvotes: 0

RJParikh
RJParikh

Reputation: 4166

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

At that time you should check total with player_priority as you have priority field in your table and consider player_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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

If I understood your requirement correctly, you want, in case of ties, to place the player having the lowest afternoon score on top.

You could try this:

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

Upvotes: 1

Related Questions