MoLi0003 MoLi0003 - 2 years ago 95
SQL Question

SQL unknown column in where clause when using max

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
FROM Chats
LEFT JOIN Messages ON Chats.cid = Messages.cid
WHERE maxdate = mdate
GROUP BY cid
ORDER BY maxdate DESC

Answer Source

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 ON clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download