Mysql query inner join two tables with group by last ID

Question

I'm using two tables in the database:

The "members" table:

  id   |   name    
-----------------------
  1    |   John
  2    |   Mike
  3    |   Jane
  4    |   Marry

The "messages" table:

  message_id  |   text   |   from   |   to
 ---------------------------------------------  
      1       |  text 1  |     1    |    2 
      2       |  text 2  |     1    |    3
      3       |  text 3  |     1    |    4  
      4       |  text 4  |     2    |    1  
      5       |  text 5  |     1    |    2

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

My current mysql query looks:

SELECT max(messages.message_id) as max_mess, messages.from, messages.to, members.name, messages.text 
FROM messages INNER JOIN members on members.id = messages.to  
WHERE messages.from = '1' 
GROUP BY messages.from  
ORDER BY max_mess DESC

Required result should look like:

  max_mess  |   from   |    to   |   name  |   text  | total_messages
 --------------------------------------------------------------------
     5      |    1     |    2    |   Mike  |  text 5 |      3
     3      |    1     |    4    |   Marry |  text 3 |      1
     2      |    1     |    3    |   Jane  |  text 2 |      1

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

Why does total_messages to user 2 equal 3? – Strawberry Aug 01 '14 at 11:20 — Strawberry, Aug 01 '14 at 11:20

Sebastian Brosch · Accepted Answer · 2018-01-15T17:43:24.127

You can use one of the following solutions:

solution #1 (using a additional JOIN):

SELECT 
    m1.max_mess, 
    m1.from, 
    m1.to, 
    m1.name, 
    m2.text, 
    m1.total_messages 
FROM (
    SELECT 
        MAX(message_id) AS max_mess, 
        messages.from, 
        messages.to,
        members.name, 
        COUNT(message_id) AS total_messages 
    FROM messages INNER JOIN members ON members.id = messages.to  
    WHERE messages.from = 1 
    GROUP BY messages.from, messages.to  
) m1 LEFT JOIN messages m2 ON m1.max_mess = m2.message_id
ORDER BY m1.max_mess DESC;

demo: http://sqlfiddle.com/#!9/7c73b6/1/0

Note: This solution should be faster than the second solution. You can find more details on the following answer on StackOverflow: JOIN vs. Sub-Query.

solution #2 (using a sub-select):

SELECT 
    MAX(message_id) AS max_mess, 
    m1.from, 
    m1.to,
    members.name, 
    (SELECT messages.text FROM messages WHERE message_id = MAX(m1.message_id)) as text,
    COUNT(message_id) AS total_messages
FROM messages m1 INNER JOIN members ON members.id = m1.to 
WHERE m1.from = 1 
GROUP BY m1.from, m1.to  
ORDER BY max_mess DESC;

demo: http://sqlfiddle.com/#!9/7c73b6/2/0

it does not return the right message text for selected message (return "text 1" instead "text 5" for max_mess 5) — Sergio, Aug 01 '14 at 10:28
This is what I need. Thx. Which query is better to use? The first one is faster? — Sergio, Aug 01 '14 at 11:40
The first one should be faster: [http://stackoverflow.com/questions/2577174/join-vs-sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query) — Sebastian Brosch, Aug 01 '14 at 12:15

score 0 · Answer 2 · answered Aug 01 '14 at 09:21

This is one query. If you want to remove the JOIN statement then you should use

SELECT max(messages.message_id) as max_mess, messages.from, messages.to, members.name, messages.text 
FROM messages, members  
WHERE messages.from = '1' AND members.id = messages.to
GROUP BY messages.from  
ORDER BY max_mess DESC

score 0 · Answer 3 · answered Aug 01 '14 at 09:48

0

i think you are searching for this:

SELECT MAX(messages.message_id) AS max_mess, messages.from, messages.to, members.name, messages.text,, SUM(`from`) total_messages 
FROM messages INNER JOIN members ON members.id = messages.to  
WHERE messages.from = '1' 
GROUP BY messages.from,messages.to  
ORDER BY max_mess DESC;

answered Aug 01 '14 at 09:48

catalinetu

630
6
12

@catalinetu: it does not return the right message text for selected message (return "text 1" instead "text 5" for max_mess 5) – Sergio Aug 01 '14 at 10:28

Mysql query inner join two tables with group by last ID

3 Answers3