CODEWITHSUNDEEP
postgresqlcountgroup-bysql-order-by
Diego
Diego

Reputation: 964

PostgreSQL Order by not working, it doesn’t returns values according to the order

I have two tables players and scores. On the player table I have two fields, name and id (PK) On the scores I have several fields [id_score(PK),winner and looser are foreign key to the id of the player] id tournament and id match.

Players
 id_players |   name    
------------+-----------
         41 | Antonia
         42 | Roberto
         43 | Luis
         44 | Pedro
         45 | Fernando
         46 | Alejandra
         47 | Rene
         48 | Julieta

Scores
id_score | id_matches | winner | looser | id_tournament | round 
----------+------------+--------+--------+---------------+-------
       19 |         22 |     41 |     42 |            21 |     1
       20 |         23 |     43 |     44 |            21 |     1
       21 |         24 |     45 |     46 |            21 |     1
       22 |         25 |     47 |     48 |            21 |     1
       23 |         26 |     43 |     41 |            21 |     2
       24 |         27 |     45 |     47 |            21 |     2
       25 |         28 |     42 |     44 |            21 |     2
       26 |         29 |     48 |     46 |            21 |     2
       27 |         30 |     43 |     45 |            21 |     3
       28 |         31 |     42 |     48 |            21 |     3
       29 |         32 |     41 |     47 |            21 |     3
       30 |         33 |     46 |     44 |            21 |     3

I'm trying to get a query that contains the following:
id of the player
name of the player
number of wins
total games

Currently I have these query. It kind of works. When I do the order by, it's not returning items on the correct orders

select p.id_players,p.name,count (s.winner) as wins, max(s.round)
from players p 
left join scores s on s.winner = p.id_players 
group by id_players, s.winner
order by s.winner desc;

 id_players |   name    | wins | max 
------------+-----------+------+-----
         41 | Antonia   |    2 |   3
         42 | Roberto   |    2 |   3
         43 | Luis      |    3 |   3
         45 | Fernando  |    2 |   2
         46 | Alejandra |    1 |   3
         47 | Rene      |    1 |   1
         48 | Julieta   |    1 |   2
         44 | Pedro     |    0 |

Upvotes: 0

Views: 6749

Answers (2)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31163

The ordering works as it should. The problem is you are asking it to order by s.winner which refers to the ID of the winning player. If you want to order them based on most wins, you need to use ORDER BY wins DESC, maybe with a secondary order clause for ties. Then the rows would start with the player with maximum number of wins.

A bit more logical query would be

SELECT s.winner, p.name, count(s.winner) as wins, max(s.round)
  FROM players p
    LEFT JOIN scores s ON s.winner = p.id_players 
  GROUP BY s.winner
  ORDER BY wins DESC;

This way you don't need two GROUP BYs since s.winner is the same as p.id_players anyway.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Sometimes, it is easier to use subqueries:

select p.*,
       (select count(*)
        from scores s
        where p.id_players in (s.winner, s.loser)
       ) as GamesPlayed,
       (select count(*)
        from scores s
        where p.id_players in (s.winner)
       ) as GamesWon
from players p
order by GamesWon desc;

If the maximum of the round is the number of games played, then similar logic will get that.

Upvotes: 1

Related Questions