Fetch up to 2 users of each team with the greatest result MYSQL

Question

| id | user  | team   | month | result |
|----|-------|--------|-------|--------|
| 1  | Joe   | red    | sept  | 100    |
| 2  | Joe   | red    | oct   | 40     |
| 3  | Jim   | red    | sept  | 70     |
| 4  | Jim   | red    | oct   | 50     |
| 5  | Susy  | red    | sept  | 40     |
| 6  | Tim   | blue   | sept  | 60     |
| 7  | Tim   | blue   | oct   | 100    |
| 8  | Betty | blue   | sept  | 70     |
| 9  | Dave  | blue   | sept  | 20     |
| 10 | Stan  | green  | oct   | 40     |
| 11 | Alan  | green  | sept  | 80     |
| 12 | Tina  | green  | oct   | 100    |
| 13 | Tina  | green  | sept  | 30     |
| 14 | Rick  | yellow | oct   | 50     |
| 15 | Ellen | yellow | oct   | 60     |

Is this possible.

I need to fetch up to 2 users of each team with the greatest result.

Eg the following result is the 2 players from each team with the highest score:

| 1  | Joe   | red    | sept  | 100    |
| 3  | Jim   | red    | sept  | 70     |
| 7  | Tim   | blue   | oct   | 100    |
| 8  | Betty | blue   | sept  | 70     |
| 12 | Tina  | green  | oct   | 100    |
| 11 | Alan  | green  | sept  | 80     |
| 15 | Ellen | yellow | oct   | 60     |
| 14 | Rick  | yellow | oct   | 50     |

Or is the only real way to do this with multiple queries?

Answer 1

You can do it like this:

SELECT * FROM 
    (SELECT t.id,t.team,count(*) as rnk
    FROM YourTable t
    INNER JOIN YourTable s
     ON(t.id = s.id and t.team = s.team and t.result >= s.result))x
WHERE rnk <= 2

Answer 2

You can try the following:

select t.*
from `tbl` t
where ( select count(*)
        from `tbl`
        where `team` = t.`team` and `result` >= t.`result` ) < 3

SQLFiddle