+------------+--------+--------+
| tournament | player | Points |
+------------+--------+--------+
| 1 | Bob | 9 |
| 1 | Jeff | 2 |
| 1 | Steve | 1 |
| 2 | Bob | 5 |
| 2 | Jeff | 3 |
| 2 | Steve | 8 |
| 2 | Paul | 4 |
| 3 | Bob | 9 |
| 3 | Jeff | 3 |
| 3 | Steve | 2 |
+------------+--------+--------+
Bob: 2
Steve: 1
Jeff: 0
Paul: 0
select /*3. and finally we left join the original table to get all names and display the number of wins again*/
outerq.player,
coalesce(subq.wins, 0) as wins
from
Table1 outerq
left join (
select /*2. Now we determine how much tournaments each player won*/
ot.player,
count(*) as wins
from (
select /*1. First we determine the winners per tournament here*/
*
from
Table1 t
where Points = (select max(Points) from Table1 st where st.tournament = t.tournament)
) ot
group by ot.player
) subq on outerq.player = subq.player
group by outerq.player
order by wins desc
