CODEWITHSUNDEEP
mysql
CIB
CIB

Reputation: 785

MySQL move one of the returned results the bottom

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

player_id, 
player_name, 
player_morning_score, 
player_afternoon_score,
player_priority,
player_no_return

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.

Upvotes: 1

Views: 54

Answers (2)

sagi
sagi

Reputation: 40491

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.

Upvotes: 0

Stan
Stan

Reputation: 1430

Check IF statement in SELECT. 'IF' in 'SELECT' statement - choose output value based on column values

You can select column if player played in the morning or not placing 1 or 0 value. Then order by this field and total

Upvotes: 0

Related Questions