Re: Selecting the most recent timestamptz

Alexander Farber 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)
>
> I try to get the record with the latest timestamp by adding a NOT EXISTS condition -
>
> # select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10
> and not exists (select 1 from words_moves x where m.mid=x.mid AND x.played > m.played);
> 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)
>
> Why are still 2 records returned? I am probably overlooking something simple, sorry...

You are only checking if there is a later timestamp *for the same "mid"*.

Since the two rows have different "mid", they are not compared.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com