title: How to build this SQL Query : Select conversation with last message
body: <p>Hello I am a newbie in sql and have great headache with such a demand:</p>
  
  <p>There are 2 tables : CONVERSATION and MESSAGE</p>
  
  <pre><code>CONVERSATION: _id INTEGER, description TEXT
  MESSAGE: _id INTEGER, message TEXT, conversation_id INTEGER
  </code></pre>
  
  <p>each conversation could contain multi messages.</p>
  
  <p>I need to get all conversation with their last message(max _id):
  Conversation1 + Last Message of that Conversation(if not exist then each column null).
  for example</p>
  
  <pre><code> _id       description     message_id              message             conversation_id
  
   1         conv 1            7             "last message in conv 1           1 
  
   2         conv 2            12             "last message in conv 2"         2
  
   3         conv 3           null                   null                     null  
  (this conversation has no message)
  
   ......
  
  15         conv 15          212            "last message in conv 15"         15
  </code></pre>
  
  <p>How could i write the query?</p>
  
  <p>btw, if i need to query in android with sqlite, do i need to query a raw sql or can use some help class for this query?</p>
  
  <p>Thanks everyone who spend time on my question!</p>
  
  <p>－－－－－－－－－－－－－－－</p>
  
  <p>I just wrote a sql query could i please Profies have a look?</p>
  
  <p>SELECT conversation.*, message._id as message_id, message.message, message.conversation_id
  FROM conversation LEFT JOIN message on CONVERSATION._ID=message.conversation_id
  group by message.conversation_id having message._id = max(message.id)</p>
  
  <p>==============================</p>
  
  <p>finally i make a sql statement as below and it works:</p>
  
  <p>select * from conversation left join (select max(_id), conversation_id from message group by conversation_id) on conversation._id = conversation_id</p>
  
  <p>Thanks for everyone who helped me :)</p>