I am building a web-based chatting system (mysql, php, jquery), listed by conversations, where each conversation shows the last message sent only. (like fb).
the table has the next fields, where
id_message, id_sender, id_receiver, conversation_code, message_date,
SELECT * FROM table_messages
WHERE message_date IN (SELECT max(message_date)
FROM table_messages WHERE id_sender='$id_session' OR id_receiver='$id_session'
GROUP BY conversation_code)
ORDER BY message_date DESC
This is based on an inner join (dinamic) instead of in
SELECT a.* FROM table_messages as a INNER JOIN (SELECT max(message_date) as md FROM table_messages WHERE id_sender='$id_session' OR id_receiver='$id_session' GROUP BY conversation_code) t on t.md = a.message_date ORDER BY a.message_date DESC