Re: Selecting the most recent timestamptz

Hi Ken -

On Thu, Feb 22, 2018 at 10:24 AM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber <
> alexander(dot)farber(at)gmail(dot)com> wrote:
>
>> Good morning, there are these 2 records in a table:
>>
>> # select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where
>> gid=10;
>> played | mid | action | gid | uid
>> -------------------------------+-----+--------+-----+-----
>> 2018-02-19 14:42:08.46222+01 | 12 | play | 10 | 9
>> 2018-02-20 15:06:01.430634+01 | 216 | expire | 10 | 11
>> (2 rows)
>>
>
> On a related note for the list, I know of at least two other ways to do
> this. Are any of them better and worse?
>
> SELECT DISTINCT ON (gid) [fields] FROM words_moves m WHERE gid=10 ORDER BY
> gid,played DESC
> SELECT [fields] FROM words_moves m WHERE gid=10 ORDER BY played DESC limit
> 1;
>
>
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