Cfrgtkky

First, thank you so much for your help.

I have 2 tables: a conversation table and a message table, and a third table assoc_message__conversation that associates the messages to a conversation.

I need to get the latest message_id and message sent for each conversation specified, along with the conversation_id it is associated with.

Here is a db-fiddle: https://www.db-fiddle.com/f/kxRQeGUYYgQ7FTwi96hbLp/0

As you can see in this example, there are two conversations with conversation_id of 1 and 2, and there are three messages associated to each conversation. Messages 1, 2, and 3 are associated to conversation 1, and messages 4, 5 and 6 are associated to conversation 2.

I need to be able to specify the conversation_id's in the assoc_message__conversation table (IDs 1 and 2), and retrieve the latest message_id, message and the associated conversation_id sent from the message table for each conversation specified.

So the rows it should pull are:

conversation_id | message_id | message

------------------------------------------------

              1 |          3 | "Latest message"

------------------------------------------------

              2 |          6 | "Latest message"

------------------------------------------------

Thank you so much for your help!

In older versions of MySQL (< 8.0.2), we can use Derived Tables. In a Derived table, we can get the latest send_datetime value for each conversation_id. Also, it is noteworthy that you can provide your filters for conversation_id in the WHERE clause of this subquery.

We can then use this subquery`s result-set and join back to the main tables appropriately, to get the row corresponding to the latest message in a conversation.

Schema (MySQL v5.7)

View on DB Fiddle

Query #1

SELECT

  amc.conversation_id, 

  m.message_id, 

  m.message   

FROM 

  assoc_message__conversation AS amc

JOIN message AS m 

  ON m.message_id = amc.message_id 

JOIN 

(

  SELECT

    amc1.conversation_id, 

    MAX(m1.send_datetime) AS latest_send_datetime

  FROM

   assoc_message__conversation AS amc1

  JOIN message AS m1

    ON m1.message_id = amc1.message_id 

  WHERE amc1.conversation_id IN (1,2)  -- Here you provide your input filters

  GROUP BY amc1.conversation_id

) AS dt  

  ON dt.conversation_id = amc.conversation_id AND 

     dt.latest_send_datetime = m.send_datetime;

Result

| conversation_id | message_id | message        |

| --------------- | ---------- | -------------- |

| 1               | 3          | Latest message |

| 2               | 6          | Latest message |

In MySQL 8.0.2 and above, we can use Row_Number() functionality. Within a partition of conversation_id, we will determine Row Number for every message, sorted in descending order of send_datetime. In this subquery, you can provide your filters for conversation_id in the WHERE clause.

We will then use this result-set as a Derived Table, and consider only those rows, where Row Number value is 1 (as it will belong to latest send_datetime).

Schema (MySQL v8.0)

View on DB Fiddle

Query #2

SELECT 

  dt.conversation_id, 

  dt.message_id, 

  dt.message 

FROM 

(

  SELECT

    amc.conversation_id, 

    m.message_id, 

    m.message, 

    ROW_NUMBER() OVER (PARTITION BY amc.conversation_id 

                       ORDER BY m.send_datetime DESC) AS row_no 

  FROM

   assoc_message__conversation AS amc

  JOIN message AS m

    ON m.message_id = amc.message_id 

  WHERE amc.conversation_id IN (1,2)  -- Here you provide your input filters

) AS dt  

WHERE dt.row_no = 1;

Result

| conversation_id | message_id | message        |

| --------------- | ---------- | -------------- |

| 1               | 3          | Latest message |

| 2               | 6          | Latest message |

Assuming that amc_id increments for each new message, I would recommend a correlated subquery in the where clause:

select amc.*, m.message

from message m join

     assoc_message__conversation amc

     on amc.message_id = m.message_id

where amc.amc_id = (select max(amc.amc_id)

                    from assoc_message__conversation amc2

                    where amc2.conversation_id = amc.conversation_id

                   );

If you actually need to use send_datetime, then an additional join is necessary:

where m.send_datetime = (select max(m2.send_datetime)

                         from message m2 join

                              assoc_message__conversation amc2

                              on amc2.message_id = m2.message_id

                         where amc2.conversation_id = amc.conversation_id

                        )