Get highest ids in by an inner join and max id

I got two tables, conversations and messages, What I’d like to do is to make a inner join from conversations to messages.

Here is my query:

    SELECT 
    messages.msg,
    messages.`read`,
    conversations.userid,
    conversations.contactid

    FROM conversations 
        INNER JOIN messages ON 
        conversations.id = messages.convId
    WHERE conversations.id IN(443,444)

Now everything works as it should, but a last thing is that in the inner join where i use

conversations.id = messages.convId

I would like to get the highest ids only, like:

 AND MAX(messages.id)

But that doesn’t work

EDIT: I tried once to use:

LEFT JOIN messages 
    ON conversations.id = messages.convId 
        AND messages.id = MAX(messages.id)

But i got an error saying: Invalid use of group function.

Answer

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
        )

Advertisement

Answer