title: SQL - Selecting highest scores for different categories
body: <p>Lets say i've got a db with 3 tables:</p>
  
  <ul>
  <li>Players (PK id_player, name...),</li>
  <li>Tournaments (PK id_tournament, name...),</li>
  <li>Game (PK id_turn, FK id_tournament, FK id_player and score)</li>
  </ul>
  
  <p>Players participate in tournaments. Table called Game keeps track of each player's score for different tournaments)</p>
  
  <p>I want to create a view that looks like this:</p>
  
  <pre><code>    torunament_name     Winner     highest_score
      Tournament_1        Jones                300
      Tournament_2        White                250
  </code></pre>
  
  <p>I tried different aproaches but I'm fairly new to sql (and alsoto this forum)
  I tried using union all clause like:</p>
  
  <pre><code>    select * from (select "Id_player", avg("score") as "Score" from 
      "Game" where "Id_tournament" = '1' group by "Id_player" order by 
      "Score" desc) where rownum &lt;= 1 
      union all
      select * from (select "Id_player", avg("score") as "Score" from 
      "Game" where "Id_tournament" = '2' group by "Id_player" order by 
      "Score" desc) where rownum &lt;= 1;
  </code></pre>
  
  <p>and ofc it works but whenever a tournament happens, i would have to manually add a select statement to this with Id_torunament = nextvalue</p>
  
  <p>EDIT:
  So lets say that player with id 1 scored 50 points in tournament a, player 2 scored 40 points, player 1 wins, so the table should show only player 1 as the winner (or if its possible 2or more players if its a tie) of this tournament. Next row shows the winner of second tournament. I dont think Im going to put multiple games for one player in the same tournament, but if i would, it would probably count avg from all his scores.</p>
  
  <p>EDIT2:
  Create table scripts:</p>
  
  <pre><code>    create table players 
      (id_player numeric(5) constraint pk_id_player primary key, name 
      varchar2(50));
      create table tournaments 
      (id_tournament numeric(5) constraint pk_id_tournament primary key, 
      name varchar2(50));
      create table game 
      (id_game numeric(5) constraint pk_game primary key, id_player 
      numeric(5) constraint fk_id_player references players(id_player), 
      id_tournament numeric(5) constraint fk_id_tournament references 
      tournaments(id_tournament), score numeric(3));
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/rNogX.png" rel="nofollow noreferrer">RDBM screenshot</a></p>
  
  <p>FINAL EDIT:
  Ok, in case anyone is wondering I used Jorge Campos script, changed it a bit and it works. Thank you all for helping. Unfortunately I cannot upvote comments yet, so I can only thank by posting. Heres the final script:</p>
  
  <pre><code>select
     t.name, 
     p.name as winner, 
     g.score
  from
     game g inner join tournaments t
             on g.id_tournament = t.id_tournament
            inner join players p
             on g.id_player = p.id_player
            inner join 
                (select g.id_tournament, g.id_player,
                        row_number() over (partition by t.name order by 
      score desc) as rd from game g join tournaments t on 
      g.id_tournament = t.id_tournament
            ) a
         on g.id_player = a.id_player
            and g.id_tournament = a.id_tournament
            and a.rd=1
  order by t.name, g.score desc;
  </code></pre>