MySql: Select last inserted value with GROUP BY and MAX

Question

I have a table called message with 5 columns. The primary key is message_id which is auto incremented.

I am trying to select and display the last inserted message which has a related_message id of 26 by using Group By and Max.
So I want to select everything from the table with only the last inserted message which is "stackoverflow".

I tried using various methods but to no avail.

SELECT *
   FROM message
   WHERE person_send_id = :person_send_id OR person_receive_id = :person_receive_id 
   AND related_message IN (SELECT MAX(related_message) FROM message GROUP BY related_message DESC)
   ORDER BY message_id DESC

Method 2

SELECT message_id, message, person_send_id, person_receive_id, max(related_message) as `related_message`
   FROM message
   WHERE person_send_id = :person_send_id OR person_receive_id = :person_receive_id
   GROUP BY related_message DESC
   ORDER BY message_id DESC

Solution with the help of DarbyM

SELECT m1.*
  FROM message m1
  JOIN (SELECT MAX(message_id) AS messageID
        FROM message
        Group By related_message) max
  ON max.messageID = m1.message_id 
  WHERE person_send_id = :person_send_id OR person_receive_id = :person_receive_id

If one of these solutions was an answer for you, please don't forget to select one as an answer. hint hint :) — DarbyM, May 18 '17 at 22:29

score 1 · Answer 1 · answered May 18 '17 at 22:11

1

SELECT * FROM message WHERE related_message == 26 and message_id >=(SELECT Max(message_id) FROM message WHERE related_message = 26)

answered May 18 '17 at 22:11

Bleh

441
3
14

This works too but its somewhat inflexible if I decide to change the related_message to 27 for example – Viteazul May 18 '17 at 22:16

score 1 · Accepted Answer · answered May 18 '17 at 22:11

1

I believe this is what you're after:

SELECT m1.*
FROM message m1
JOIN (SELECT MAX(message_id) AS messageID 
        FROM messages
        Group By related_message) max
    ON max.messageID = m1.message_id
WHERE person_send_id IN (:person_send_id, :person_receive_id )

answered May 18 '17 at 22:11

DarbyM

1,173
2
9
25

What do you mean by m1? – Viteazul May 18 '17 at 22:12
I wasn't so sure on your exact needs for the Where part.. I extrapolated my example out of your failed attempts you posted. – DarbyM May 18 '17 at 22:12
m1 is the assigned alias i gave for "message" table. (FROM message m1) – DarbyM May 18 '17 at 22:13
It works thank you! I know im going overboard but can u please explain why you used JOIN in this case? – Viteazul May 18 '17 at 22:14
Ultimately due to the need to use aggregation, (MAX()) I believe in this example, the performance would be identical to adding the Select statement in the Join to an "AND" m1.message_id = type of statement after the WHERE. But Join is the far better habit. As it will help you create statements in the future that perform better. "That is my short answer" :) – DarbyM May 18 '17 at 22:18
Thanks for the info – Viteazul May 18 '17 at 22:19
FYI, adding an alias like m1 and max , ect.. allows for keeping lines like "ON max.messageID = m1.message_id" a bit easier to keep straight. it is also a best practices kind of thing... and in many situations within complex queries required. – DarbyM May 18 '17 at 22:23
I tested the solution extensively. A problem arises when another user (person_send_id: 50 for example) sends a message to him (person_receive_id 45) only 1 message will be displayed even if the related_message id is different – Viteazul May 18 '17 at 22:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/144604/discussion-between-darbym-and-viteazul). – DarbyM May 18 '17 at 22:41
I have a hunch what you are trying to accomplish is identical to what I just built a month-and-a-half ago. If that is the case there might be a small piece of the puzzle you're missing and your data structure. I would be happy to look into it further but unfortunately the comment section here isn't the best place to do that. – DarbyM May 18 '17 at 23:11

Ing. Gerardo Sánchez · Answer 3 · 2017-05-18T22:19:20.367

1

First suggestion:

SELECT * FROM message INNER JOIN 
    (SELECT MAX(message_id) AS max_msg_id FROM message WHERE person_send_id = :person_send_id OR person_receive_id = :person_receive_id)
AS aux_table ON aux_table.max_msg_id = message.message_id

Second suggestion:

SELECT * FROM message INNER JOIN 
    (SELECT MAX(message_id) AS max_msg_id FROM message WHERE related_msg =: related_msg)
AS aux_table ON aux_table.max_msg_id = message.message_id

edited May 18 '17 at 22:19

answered May 18 '17 at 22:15

Ing. Gerardo Sánchez

1,607
15
14

This works as well thank you ! – Viteazul May 18 '17 at 22:17
I tested the solution extensively. A problem arises when another user (person_send_id: 50 for example) sends a message to him (person_receive_id 45) only 1 message will be displayed even if the related_message id is different – Viteazul May 18 '17 at 22:32

MySql: Select last inserted value with GROUP BY and MAX

Solution with the help of DarbyM

3 Answers3