craigb88 craigb88 - 2 years ago 63
SQL Question

MySQL move one of the returned results the the botom

I'm having an issue with a MySQL query any help would be good.

Basically the project I'm doing is to record and display golf scores. I have a query that gets all the players morning score and afternoon score adds them together and displays lowest score at the top.

I have an extra field for priority which checks if two plays are on the same score and I can assign a number to display them above the other. All this works fine, the issue I have is if a player only plays the morning or afternoon they are displayed at the top because they have the lowest score. I need to add something to check if they have played both morning and afternoon if not then they go to the bottom of the list. Hope that make sense.

My database fields are


I was hoping to put a 1 in the player_no_return and then my query would check for this and move them to the bottom of my results. But I'm not sure how to go about it.

Here's my query for currently displaying them.

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'

At the minute my results look like this

Craig 10

Steve 15

Julie 21

Alex 21

Bob 25

Craig has only played the morning so would need moving to the bottom.

Answer Source

You are sorting by strings, which means you are not sorting , use back ticks for columns/aliasing : ` and not single quote mark that represent a string. Although you don't even need them(they are used for more then 1 word alias , or reserved words) :

SELECT player_id, player_name,
       player_morning_score + player_afternoon_score AS total
FROM players 
ORDER BY CASE WHEN player_afternoon_score > 0 AND player_morning_score > 0 THEN 1 ELSE 0 END DESC,
          `total`,player_priority,player_afternoon_score DESC

ORDER BY default is ASC , so you don't have to write it down, only when you want DESC.

I added you requirment (if he didn't play both, he will be on the bottom of the list) using CASE EXPRESSION in the ORDER BY clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download