Postgres Pro
Downloads
Home   >   mailing lists

Thread: Get most recent message between users?

Get most recent message between users?

From
Nick
Date:
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?

Re: Get most recent message between users?

From
Sam Mason
Date:
On Mon, Aug 17, 2009 at 04:33:11PM -0700, 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?

More details would help, but failing that how about:

  SELECT DISTINCT ON (to_user, from_user) id, to_user, from_user, created
  FROM messages
  ORDER BY to_user, from_user, created DESC;

That would only chop out id=1 from the above list.

--
  Sam  http://samason.me.uk/

Re: Get most recent message between users?

From
Steve Crawford
Date:
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

Re: Get most recent message between users?

From
nick
Date:
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...@pinpointresearch.com (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...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general