Get highest ids in by an inner join and max id

the top solution above will not work everywhere, for example postgres will give errors, a better solution to join tables and only get the max id from the other table:

SELECT conversations.*, m.* 
    FROM conversations 
    JOIN (SELECT cid, max(id) as m_id from messages group by cid) as m
        ON conversations.id = m.cid 

You are looking for the maximum value in a group. MySQL does not have any really obvious way to do this (lots of ways with a bit of trickery).

But, you are only looking for two groups. As such, you can just look for the limit in each group and combine them using union all:

SELECT m.msg, m.`read`, c.userid,
       c.contactid
FROM conversations c inner join
     ((select m.*
       from messages m
       where m.convid = 443
       order by m.id desc
       limit 1
      ) union all
      (select m.*
       from messages m
       where m.convid = 444
       order by m.id desc
       limit 1
      )
     ) m
     ON c.id = m.convId;

If you have indexes on messages(convid, id) and conversations(id), then this should be quite fast.

You can also do this using a more standard approach:

SELECT m.msg, m.`read`, c.userid,
       c.contactid
FROM conversations c inner join
     messages m
     ON c.id = m.convId
where c.convId in (443, 444) and
      m.id = (select max(id) from messages m2 where m2.convId = c.convId)

EDIT

This will work!

SELECT conversations.*, m1.* 
FROM conversations 
LEFT JOIN messages m1
    ON conversations.id = m1.cid 
    AND m1.id = (
            SELECT MAX(m2.id) 
            FROM messages m2 
            WHERE m2.cid = conversations.id
        )