CODEWITHSUNDEEP
mysqlsql
Zuker
Zuker

Reputation: 456

Get highest value from multiple columns and associated name column

I've got a table with these columns:

id | player1_name | player1_score | player2_name | player2_score | player3_name | player3_score | player4_name | player4_score | player5_name | player5_score

Given a single row, how do I get the highest playerX_score and the corresponding playerX_name?

I've tried using GREATEST(), but I can't get the playerX_name.

Upvotes: 0

Views: 38

Answers (2)

octern
octern

Reputation: 4868

I think your table structure isn't right for what you're trying to do. You want the database to know that there's some relationship between player1_name and player1_score, but that's not encoded in the table. A change that would make this much easier would be to give each player their own record, and use what you're currently calling id (which I assume is the ID for a particular game) to indicate which players go together.

It would look like this:

game_id  | player_num | player_name | score
1        | 1          | Octern      | 100
1        | 2          | Boris       | 400
1        | 3          | Jarlsberg   | 300
1        | 4          | Pete        | 40000
...

Then, to find the high scorer for a given game (in this case, game #1), you'd say:

select player_name from scores
    WHERE game_id = 1
    ORDER BY score desc
    LIMIT 1

Upvotes: 0

mathematical.coffee
mathematical.coffee

Reputation: 56925

As an aside, I think your table would be better designed as id | name | score | position | teamid, where position goes from 1 to 5 and teamid serves to group everyone in the same team together. It would make this sort of query much easier (greatest-score-per-team with associated rows).

However, here's one way to do what you want with your current table:

SELECT GREATEST(player1_score,player2_score,player3_score,
                player4_score,player5_score) as score,
       CASE GREATEST(...) -- repeat the above
         WHEN player1_score then player1_name
         WHEN player2_score then player2_name
         WHEN player3_score then player3_name
         WHEN player4_score then player4_name
         WHEN player5_score then player5_name
       END as name
FROM mytable

Upvotes: 1

Related Questions