title: PostgreSQL join to most recent record between tables
body: <p>I have two tables pertaining to this question: <code>conversations</code> has many <code>messages</code>.  The basic structure (with just the relevant columns) is as follows:</p>
  
  <pre><code>conversations (
    int id (PK)
  )
  
  create table conversation_participants (
    int id (PK),
    int conversation_id (FK conversations),
    int user_id (FK users),
    unique key on [conversation_id, profile_id]
  )
  
  create table messages (
    int id (PK),
    int conversation_id (FK conversations),
    int sender_id (FK users),
    int recipient_id (FK users),
    text body
  )
  </code></pre>
  
  <p>For each conversations entry, given a <code>user_id</code> I want to receive:</p>
  
  <ul>
  <li>all conversations that user participated in (i.e.: <code>conversations.*</code>)</li>
  <li>joined to the most recent matching message (i.e.: <code>order by messages.id desc limit 1</code>)</li>
  <li>conversations ordered by their most recent message id (i.e.: order by <code>messages.id desc</code>)</li>
  </ul>
  
  <p>Unfortunately, all the query help I can seem to find on anything like this pertains to MySQL, and that doesn't work in PostgreSQL.  The closest thing I found is <a href="https://stackoverflow.com/q/1734804/107277">this answer on StackOverflow</a> that gives an example of the <code>select distinct on (...)</code> syntax.  However, unless I'm just doing it wrong, I can't seem to get the results ordered in the correct way given the grouping constraints I need with that method.</p>