So I have a table of messages and a table of chats. Those messages have a date and the id of the chat they are in. Now I want to select the latest message of each chat. For this I need the maximum of the dates which must be the same as the date of the message. Otherwise I get a random message from this chat.
If I do so it tells me 'Unknown column 'maxdate' in 'where clause'' and I have no idea, why. I can also order by this column.
Here is my query:
SELECT Chats.cid, cname, mtext, MAX(mdate) AS maxdate
LEFT JOIN Messages ON Chats.cid = Messages.cid
WHERE maxdate = mdate
GROUP BY cid
ORDER BY maxdate DESC
You are looking for something like this:
SELECT c.cid, c.cname, m.mtext, mdate FROM Chats c LEFT JOIN Messages m ON c.cid = m.cid AND m.mdate = (SELECT MAX(m2.mdate) FROM Messages m2 WHERE m2.cid = c.id) ORDER BY mdate DESC;
Because you are using a
LEFT JOIN, it is important that the comparison to the maximum goes in the