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 :)
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.