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
SELECT player_id, player_name, (player_morning_score + player_afternoon_score) AS total
ORDER BY 'total,player_priority', 'ASC','player_afternoon_score', 'DESC'
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
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.