SQL filter rows without join

Question

I'm always "irk" by unnecessary join. But in this case, I wonder if it's possible to not use join.

This is an example of the table I have:

id | team | score
 1 |   1  |  300
 2 |   1  |  257
 3 |   2  |  127
 4 |   2  |  533
 5 |   3  |  459

This is what I want:

team | score | id
  1  |  300  |  1
  2  |  533  |  4
  3  |  459  |  5

Doing a query looking like this: (basically: who's the best player of each team)

SELECT team, MAX(score) AS score, id
FROM my_table
GROUP BY team

But I get something like that:

team | score | id
  1  |  300  |  1
  2  |  533  |  3
  3  |  459  |  5

But it's not the third player that got 533 points, so the result have no consistency.

Is it possible to get truthworthy results without joining the table with itself? How to achieve that?

Answer 1

You can do it without joins by using subquery like this:

SELECT id, team, score
FROM table1 a
WHERE score = (SELECT MAX(score) FROM table1 b WHERE a.team = b.team);

However in big tables this can be very slow as you have to run the whole subquery for every row in your table.

However there's nothing wrong with using join to filter results like this:

SELECT id, team, score FROM table1 a
INNER JOIN (
    SELECT MAX(score) score, team
    FROM table1
    GROUP BY team
    ) b ON a.score = b.score AND a.team = b.team

Although joining itself is quite expensive, this way you only have to run two actual queries regardless how many rows are in your tables. So in big tables this method can still be hundreds, if not thousands of times faster than the first method with subquery.