Think I got it right this time...
infogettable=# select distinct on (ma.match_id) ma.match_id,
max(ga.game_id) as game_id,
mo.player_id,
mo.move_annotation as latest_move
from bg_matches ma,bg_games ga,bg_moves mo
where ga.match_id = ma.match_id
and mo.game_id = ga.game_id
and mo.move_id = (select max(move_id) from bg_moves where game_id = mo.game_id)
group by ma.match_id,mo.player_id,mo.move_annotation
order by ma.match_id;
match_id | game_id | player_id | latest_move
----------+---------+-----------+--------------------
1 | 1 | 4 | Accept
2 | 2 | 7 | bar/24 9/8 6/5(2)
3 | 14 | 4 | 24/18 13/8
4 | 4 | 7 | stays on bar
5 | 5 | 13 | bar/23 11/5*
6 | 6 | 14 | bar/21 23/21 13/11
7 | 7 | 14 | Accept
8 | 11 | 4 | 13/9(2)
9 | 9 | 4 | Accept
10 | 12 | 15 | bar/24* 21/17*
11 | 13 | 13 | 8/5 6/5
(11 rows)
Thanks for your help, Jonathan, and the "distinct on" seems to be needed.
