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...
Thank you
Alex
P.S. In case more details are needed -
# explain 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);
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.28..43.78 rows=1 width=29)
-> Seq Scan on words_moves m (cost=0.00..27.18 rows=2 width=29)
Filter: (gid = 10)
-> Index Scan using words_moves_pkey on words_moves x (cost=0.28..8.29
rows=1 width=16)
Index Cond: (m.mid = mid)
Filter: (played > m.played)
(6 rows)
# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
--------+--------------------------+-----------+----------+------------------------------------------
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY (mid)
REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
