Re: Selecting the most recent timestamptz

yes, your suggestions work for me too:

# select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10 order by m.played desc limit 1;
            played             | mid | action | gid | uid
-------------------------------+-----+--------+-----+-----
 2018-02-20 15:06:01.430634+01 | 216 | expire |  10 |  11
(1 row)

words=> select distinct on (gid) m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10 order by m.gid, m.played desc;
            played             | mid | action | gid | uid
-------------------------------+-----+--------+-----+-----
 2018-02-20 15:06:01.430634+01 | 216 | expire |  10 |  11
(1 row)

# explain select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10 order by m.played desc limit 1;
                                QUERY PLAN
---------------------------------------------------------------------------
 Limit  (cost=27.19..27.19 rows=1 width=29)
   ->  Sort  (cost=27.19..27.19 rows=2 width=29)
         Sort Key: played DESC
         ->  Seq Scan on words_moves m  (cost=0.00..27.18 rows=2 width=29)
               Filter: (gid = 10)
(5 rows)

# explain select distinct on (gid) m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10 order by m.gid, m.played desc;
                                QUERY PLAN
---------------------------------------------------------------------------
 Unique  (cost=27.19..27.19 rows=2 width=29)
   ->  Sort  (cost=27.19..27.19 rows=2 width=29)
         Sort Key: played DESC
         ->  Seq Scan on words_moves m  (cost=0.00..27.18 rows=2 width=29)
               Filter: (gid = 10)
(5 rows)

Actually my real query was a bit bigger:

# select
g.finished, g.gid, g.played1, g.played2, g.state1, g.state2, g.score1, g.score2, m.action
from words_games g left join words_moves m on g.gid=m.gid
and not exists (select 1 from words_moves x where m.gid=x.gid AND x.played > m.played)
where reason is null and finished is not null;

           finished            | gid |            played1            |            played2            | state1 | state2 | score1 | score2 | action
-------------------------------+-----+-------------------------------+-------------------------------+--------+--------+--------+--------+--------
 2018-02-19 17:05:03.689277+01 |  72 | 2018-02-19 17:03:57.329402+01 | 2018-02-19 17:05:03.689277+01 | won    | lost   |      4 |      0 | resign
 2018-02-19 17:49:40.163458+01 |  63 | 2018-02-19 16:38:18.686566+01 | 2018-02-19 17:49:40.163458+01 | won    | lost   |      5 |      0 | resign
 2018-02-19 17:53:47.904488+01 |  89 | 2018-02-19 17:52:20.34824+01  | 2018-02-19 17:53:47.904488+01 | won    | lost   |      0 |      0 | resign
 2018-02-19 18:19:42.10843+01  | 102 | 2018-02-19 18:10:03.358555+01 | 2018-02-19 18:19:42.10843+01  | won    | lost   |     13 |      0 | resign
 2018-02-19 19:11:25.984277+01 | 117 | 2018-02-19 18:59:40.940102+01 | 2018-02-19 19:11:25.984277+01 | won    | lost   |     13 |      0 | resign
 2018-02-19 19:56:11.491049+01 | 128 | 2018-02-19 19:51:40.209479+01 | 2018-02-19 19:56:11.491049+01 | won    | lost   |      5 |      0 | resign
........

Regards

Alex