sql [db2] select statement with condition

db2, select, sql

Solution

This should do it:

SELECT
    game_id, 
    CASE WHEN p1.score > p2.score THEN p1.username ELSE p2.username END AS winner
FROM
    player1 p1
INNER JOIN
    player2 p2 ON p1.game_id = p2.game_id
ORDER BY 
    p1.game_id ASC

As Darius mentioned, you may wish to adjust the CASE statement according to the situation of a Draw, you may change it to something like this:

CASE WHEN p1.score > p2.score THEN p1.username WHEN p1.score < p2.score THEN p2.username ELSE 'Draw' END AS winner

It really depends on what you want to display in that scenario.

Problem

I have two tables. player1 has rows username, score, game_id, ... player2 has rows username, score, game_id... how do you select the username with the biggest score for the same game_id? what I have is ``` SELECT player1.username winning, player1.points, player1.game_id FROM player1 INNER JOIN player2 ON player1.game_id = player2.game_id WHERE player1.points > player2.points ``` and ``` SELECT player2.username winning, player2.points, player2.game_id FROM player1 INNER JOIN player2 ON player1.game_id = player2.game_id WHERE player2.points > player1.points ```

Original source