Search Postgresql Archives

Selecting the most recent timestamptz

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux