On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber < alexander.far...@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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
