From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Nick <nboutelier(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Get most recent message between users? |
Date: | 2009-08-18 01:03:12 |
Message-ID: | 4A89FDD0.8050004@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | nick | 2009-08-18 01:19:22 | Re: Get most recent message between users? |
Previous Message | Greg Stark | 2009-08-18 00:59:45 | Re: Failover, Wal Logging, and Multiple Spares |