Postgres Pro
Downloads
Home   >   mailing lists

Thread: Selecting the most recent timestamptz

Selecting the most recent timestamptz

From
Alexander Farber
Date:
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



Re: Selecting the most recent timestamptz

From
Laurenz Albe
Date:
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

Re: Selecting the most recent timestamptz

From
Alexander Farber
Date:
Ahh, thank you Laurenz -

On Thu, Feb 22, 2018 at 10:18 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

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.

no it works -

# 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.gid=x.gid AND x.played > m.played);
            played             | mid | action | gid | uid
-------------------------------+-----+--------+-----+-----
 2018-02-20 15:06:01.430634+01 | 216 | expire |  10 |  11
(1 row)
 

Re: Selecting the most recent timestamptz

From
Alexander Farber
Date:
s/no it works/now it works/

Re: Selecting the most recent timestamptz

From
Ken Tanzer
Date:
On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber <alexander.farber@gmail.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)

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


In your example, you have different values for mid.  I'm thinking you meant gid?

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.gid=x.gid AND x.played > m.played);

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;

Cheers,
Ken

--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Selecting the most recent timestamptz

From
Alexander Farber
Date:
Hi Ken -

On Thu, Feb 22, 2018 at 10:24 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber <alexander.farber@gmail.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