Re: Get most recent message between users? - Mailing list pgsql-general

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/