Re: Selecting the most recent timestamptz
On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber <alexander.farber@gmail.com> wrote:
AlexThank youWhy are still 2 records returned? I am probably overlooking something simple, sorry...I try to get the record with the latest timestamp by adding a NOT EXISTS condition -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)
# 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)
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.
В списке pgsql-general по дате отправления: