There are two tables: dialog(id,firstname) and message(id, dialog_id, text, time) . You must select all dialog entries, with each dialog entry selecting the last message entry associated with this dialog

dialog.id | dialog.firstname | message.text | message.time

Please your help, comrades.

1 answer 1

Nothing complicated. We join, as a bridge, to the table dialog for each entry, the last id from the message table, then append the remaining fields:

 select d.*, mess.text, mess.time from dialog d left join (select dialog_id, max(id) as max_id /*либо max(time)*/ from message group by dialog_id) m ON d.id = m.dialog_id join message mess ON m.max_id = mess.id
  • It works, but having come around it came to this option = SELECT chat.id, message.time, message.text FROM chat, message WHERE message.id = (SELECT max (id) FROM message WHERE message.chat_id = chat.id) GROUP BY chat .id ORDER BY message.id DESC - Vladislav
  • @Vladislav does it work for you exactly? Group by extra, subqueries often work longer than joins - Denis
  • oddly enough, but the query is working correctly, but I take into account speed - Vladislav