Forum OpenACS Q&A: Need some help on a query!

Posted by Ola Hansson on 05/04/01 03:14 PM

Hello again, I'm almost embarrassed asking for help as what I'm trying to do (a backgammon service) is all joy and no work... (Well, the joy takes precedence😊)
Nonetheless, I'm stuck for the moment on the query below and could use some help.

A match can have several games and in turn a game can have several moves...
My query returns one row for each match and the highest (latest) game_id for each match but it doesn't give me the player_id or latest_move from the latest game in the match😟

How can I do that?

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
    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 | 10/6 9/1 9/5
        2 |       2 |         6 | 24/21 13/11*
        3 |      10 |         4 | 11/5 8/5
        4 |       4 |         7 | 13/3(2)
        5 |       5 |         7 | 13/10* 13/7
        6 |       6 |         7 | 13/10 13/9
        7 |       7 |         4 | 10/2
        8 |      11 |         4 | 13/10 11/10
        9 |       9 |         4 | 13/6
       10 |      12 |         9 | 13/7
       11 |      13 |         9 | 13/6
(11 rows)

Thanks,

2: Response to Need some help on a query! (response to 1)

Posted by Jonathan Ellis on 05/04/01 05:27 PM

you need to select the whole row from where game_id is the max,

select ...
from ...
where mo.game_id = (select max(game_id) from moves, ... where ...)

you shouldn't need the distinct, either.

3: Response to Need some help on a query! (response to 1)

Posted by Ola Hansson on 05/04/01 08:57 PM

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.

4: Response to Need some help on a query! (response to 1)

Posted by Jonathan Ellis on 05/04/01 09:28 PM

It doesn't need to be that complicated.

The reason you don't need distinct is that you can join the tables together such that only one row is returned per match, which is what I was trying to get at. I missed the fact that you will need two subqueries, since you need the latest game w/in a match AND the latest move w/in a game.

Here is what I mean. My tables' pkeys are all called id instead of table_id but the sense should be clear:

select ma.id as match, mo.id as move, ga.id as game, mo.latest_move
from matches ma, moves mo, games ga
where ga.match_id = ma.id
  and ga.id = (select max(id) from games ga2 where ga2.match_id = ma.id)
  and mo.game_id = ga.id
  and mo.id = (select max(id) from moves mo2 where mo2.game_id = ga.id)

5: Response to Need some help on a query! (response to 1)

Posted by Ola Hansson on 05/04/01 11:56 PM

Jonathan, thanks to you the query is *much* quicker than the one I tried before. If you come to my site when it is released you can count on rolling a lot of sixes😉 Here's the final version:

set query "select ma.*,
    ga.game_id,
    mo.player_id,
    mo.move_annotation as latest_move,
    ua.first_names || ' ' || ua.last_name as player_a_name,
    ub.first_names || ' ' || ub.last_name as player_b_name
from bg_matches ma,bg_games ga,bg_moves mo,users ua,users ub
where ga.match_id = ma.match_id
    and ga.game_id = (select max(ga2.game_id) from bg_games ga2 where ga2.match_id = ma.match_id)
    and mo.game_id = ga.game_id
    and mo.move_id = (select max(mo2.move_id) from bg_moves mo2 where mo2.game_id = ga.game_id)
    and (ma.player_a = 4 or ma.player_b = 4)
    and ma.player_a = ua.user_id
    and ma.player_b = ub.user_id
$extra_where_clause
order by ma.match_id desc"