title: SQL filter rows without join
body: <p>I'm always "irk" by unnecessary join. But in this case, I wonder if it's possible to not use join.</p>
  
  <p>This is an example of the table I have:</p>
  
  <pre><code>id | team | score
   1 |   1  |  300
   2 |   1  |  257
   3 |   2  |  127
   4 |   2  |  533
   5 |   3  |  459
  </code></pre>
  
  <p>This is what I want:</p>
  
  <pre><code>team | score | id
    1  |  300  |  1
    2  |  533  |  4
    3  |  459  |  5
  </code></pre>
  
  <p>Doing a query looking like this:
  (basically: who's the best player of each team)</p>
  
  <pre><code>SELECT team, MAX(score) AS score, id
  FROM my_table
  GROUP BY team
  </code></pre>
  
  <p>But I get something like that:</p>
  
  <pre><code>team | score | id
    1  |  300  |  1
    2  |  533  |  3
    3  |  459  |  5
  </code></pre>
  
  <p>But it's not the third player that got 533 points, so the result have no consistency.</p>
  
  <p>Is it possible to get truthworthy results without joining the table with itself? How to achieve that?</p>