Advanced MySQL IF function

Question

I am trying to do an IF function in MySQL that i thought would be pretty straightforward, but seems to be a little for complex.

Here's the idea:

SELECT IF(score1>score2, (name1,score1,name2,score2),(name2,score2,name1,score1)) AS (winner,w_score,loser,l_score) FROM game table

what I am trying to do is test score1 > score2, depending on the result, order the fields I want.

Answer 1

Considering if score1 ,score2 ,name1 ,name2 are the column names in your game table , you could do like this :

SELECT
     IF( score1 > score2 ,name1 ,name2 ) AS winner
    ,GREATEST( score1 ,score2 ) AS w_score
    ,IF( score1 > score2 ,name2 ,name1 ) AS loser
    ,LEAST( score1 ,score2 ) AS l_score
FROM
    game

Answer 2

It looks like what you need is four different IF statements:

SELECT 
    IF(score1 > score2, name1, name2) AS winner,
    IF(score1 > score2, score1, score2) AS w_score,
    IF(score1 > score2, name2, name1) AS loser,
    IF(score1 > score2, score2, score1) AS l_score
FROM game_table

It's not the prettiest thing, but as the comparison is pretty straightforward, it should behave fine.

Answer 3

I offer a simple solution:

SELECT name1 AS winner, score1 AS w_score, name2 AS looser, score2 AS l_score
FROM game table WHERE score1 > score2
UNION
SELECT name2 AS winner, score2 AS w_score, name1 AS looser, score1 AS l_score
FROM game table WHERE score2 > score1