Mysql query inner join two tables with group by last ID -

I am using two tables in the database:

"member" table:

id | Name ----------------------- 1 | John 2 | Mike 3 Jane 4 | Marriage

"message" table:

  message_id | Text | From From --------------------------------------------- 1. Lesson 1 | 1 | 2 2 | Lesson 2 | 1 | 3 3 Lesson 3 1 | 4 4 lessons4 | 2 | 1 5 | Lesson 5 | 1 | 2  

I need to get the messages sent by a specific user, sorted by the last message sent and count the total number of messages sent to the specific user is.

My existing MySQL query looks:

  Max max_mess, messages.from, messages.to, members.name, messages.text as message SELECT max (messages .message_id) Join members from members .id = messages.to Message by WHERE messages.from = '1' group. ORDER max_mess DESC  

should necessarily look like this:

  Max_mess | From To Name | Text | Total_ Message ------------------------------------------------ - ------------------- 5 | 1 | 2 | Mike | Lesson 5 | 3 3 1 | 4 | Marriage | Lesson 3 1 2 | 1 | 3 | Jane | Lesson 2 | 1  

Is it possible to use only one mysql query and what should be changed in the current query?

  - Select solution # 1 with additional # m1.max_mess, m1.from Members from Inner members as a total message, from m1.to, m1.name, m2.text, m1.total_messages (SELECT max (message_id) max_mess, messages.from, messages.to, members.name, COUNT (message_id) Join Iid = messages.toWHERE messages.from = 1 Message by GROUP., Messages.to) M1 Left job message M2OnM 1.Max_Mess = M2. Message_ID order M1. Max_meas DESC; - Solution # 2 SELECT MAX (message_id) with sub-selection Maximum MAX_mess, m1.from, m1.to, members.name, (Select message from message. Message where message_ id = MAX (m1.message_id)) of text In the form, COUNT (message_id) messages in the form of total message join M1 inner members members IID = M1. WHERE m1.from = 1 group by M1. From, M1. Max_mess DESC for order;  

You can find a demo here: