Selecting the most recent timestamptz
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
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
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)
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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
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