How to build this SQL Query : Select conversation with last message

Hello I am a newbie in sql and have great headache with such a demand:

There are 2 tables : CONVERSATION and MESSAGE

CONVERSATION: _id INTEGER, description TEXT
MESSAGE: _id INTEGER, message TEXT, conversation_id INTEGER

each conversation could contain multi messages.

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

 _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

How could i write the query?

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?

Thanks everyone who spend time on my question!

---------------

I just wrote a sql query could i please Profies have a look?

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)

==============================

finally i make a sql statement as below and it works:

select * from conversation left join (select max(_id), conversation_id from message group by conversation_id) on conversation._id = conversation_id

Thanks for everyone who helped me :)

Answers


This approach uses a correlated subquery to get the last message id. It then joins that back to the messages table to get the additional information:

select c.*, m._id, m.message
from (select c.*,
             (select max(_id) as maxid
              from message m
              where c._id = m.conversationid
             ) as messageid
      from conversation c
     ) c join
     message m
     on c.messageid = m._id;

The columns that we can specify in SELECT statement must be a subset of the ones on which GROUPing is done. The query you wrote did not follow this rule, so you might be getting syntax error.

SELECT con.*, mes._id as message_id, mes.message, mes.conversation_id 
FROM conversation con
LEFT JOIN  
(
SELECT mes1._id, mes1.message, mes1.conversation_id
FROM message mes1 
INNER JOIN
(SELECT MAX(_id) as _maxid
FROM message
GROUP BY conversation_id) mes2
ON mes1._id = mes2._maxid
)mes
ON con._id = mes.conversation_id

Let me know if this worked.


Need Your Help

PowerShell module list different between PowerShell terminal and C# program

c# .net powershell

I am trying to use the MSOnline module in PowerShell in my C# application. When I run Get-Module -ListAvailable from within a PowerShell terminal, I can see MSOnline in the list of modules. However...