Thank you and sorry for the vagueness. The answer I was looking for was Steves last suggestion - getting the newest messages between all users which would return... 6 | 1 | 2 | 2009-07-10 5 | 2 | 3 | 2009-06-10 3 | 1 | 4 | 2009-07-21 2 | 1 | 3 | 2009-06-21 On Aug 17, 6:03 pm, scrawf...@xxxxxxxxxxxxxxxxxxxx (Steve Crawford) wrote: > Nick wrote: > > messages (id, to_user, from_user, created) > > 1 | 1 | 2 | 2009-05-21 > > 2 | 1 | 3 | 2009-06-21 > > 3 | 1 | 4 | 2009-07-21 > > 4 | 2 | 1 | 2009-05-10 > > 5 | 2 | 3 | 2009-06-10 > > 6 | 1 | 2 | 2009-07-10 > > > How could I get the most recent message between two users? > > Depends on what you want (all untested). > > If you want the most recent message from user 1 to user 2 then: > SELECT ... where from_user=1 and to_user=2 order by created desc limit 1; > > If you want the most recent message _between_ two users (either > direction), then you need to include both directions in the where clause: > where (from_user=1 and to_user=2) or (from_user=2 and to_user=1)... > > If you want the most recent message time for all messages for all > user-pairs (one direction) > ...to_user, from_user, max(created)...group by to_user,from_user > > Or most-recent between all user-pairs: > ...int4smaller(from_user,to_user), int4larger(from_user,to_user), > max(created) group by int4smaller(from_user,to_user), > int4larger(from_user,to_user) > > Optimizing some of these could be "fun", though. > > Cheers, > Steve > > -- > Sent via pgsql-general mailing list (pgsql-gene...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
