CODEWITHSUNDEEP
sqlpostgresql
Will H.
Will H.

Reputation: 15

How do I merge two columns into one? PostgreSQL

I run a database for a bowling league and each week, we all bowl 2 games. Game 1 score is put into game1_score column, Game 2 score is put into game2_score column and I get an output like this:

Table name: player_scores

season_id|nick_name         |g1_score|g2_score|
---------+------------------+--------+--------+
        2|Axel              |     133|     210|
        2|Benjie B          |      91|     163|
        2|Steveball         |     149|     159|
        2|Shadynasty        |     132|     157|
        2|MatT K            |     122|     151|

I am looking for a way to find the highest scores, regardless if it's game 1 or game 2. My expected result would be:

season_id|nick_name         |high_game|
---------+------------------+--------+
        2|Axel              |     210|
        2|Benjie B          |     163|
        2|Steveball         |     159|
        2|Shadynasty        |     157|
        2|MatT K            |     151|
        2|Steveball         |     149|
        2|Axel              |     133|
        2|Shadynasty        |     132|
        2|MatT K            |     122|
        2|Benjie B          |      91|

All I've been able to do so far is get the max from game 1 or game 2, as such:

season_id|nick_name         |high_game|
---------+------------------+--------+
        2|Axel              |     210|
        2|Benjie B          |     163|
        2|Steveball         |     159|
        2|Shadynasty        |     157|
        2|MatT K            |     151|

Is there a way to do this or should I be inserting all scores (whether it's game 1 or game 2) into the same column moving forward?

I am using PostgreSQL 13.3.

I appreciate any help and insights you can give or even if attempting. Thank you so much!

Upvotes: 0

Views: 2823

Answers (4)

Will H.
Will H.

Reputation: 15

select nick_name, g1_score high_score 
from score_players
where season_id = 2
union
select nick_name, g2_score high_score 
from score_players
where season_id = 2
order by high_score desc

ended up working for me, thank you everyone for taking a look!

Upvotes: 0

Belayer
Belayer

Reputation: 14934

You would be better off normalizing your data with a result being that games as in a single column with an additional column indicating the particular game. However, you can get what your looking for with the current structure. Instead of selecting (or in addition) use the greatest function on g1_score and g2_score.

select ps.season_id
     , ps.nick_name
     , ps.g1_score
     , ps.g2_score
     , greatest(ps.g1_score, ps.g2_score) high_game
from player_scores ps 
order by greatest(ps.g1_score, ps.g2_score) desc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You seem to want to unpivot the data and sort:

select ps.nickname, v.score
from player_scores ps cross join lateral
     (values (g1_score), (g2_score) ) v(score)
order by v.score desc;

Upvotes: 0

Dominik
Dominik

Reputation: 103

following example works for me

with highscores as (
                select season_id, nickname, g1_score as high_game 
                from player_scores ps1
                union 
                select season_id, nickname, g2_score as high_game 
                from player_scores ps2
               )
select * from highscores order by high_game desc

Upvotes: 2

Related Questions