simple SQL query giving Invalid use of group function

Question

Can anyone tell me why I'm getting Invalid use of group function and how to stop it?

SELECT Name, Message
FROM flux_chat_messages
WHERE id >= ( MAX( id ) -5 )
ORDER BY id ASC

Answer 1

You cannot use MAX() in a WHERE. So wrap it in a subquery like:

SELECT Name, Message
FROM flux_chat_messages
WHERE id >= (SELECT MAX( id ) - 5 FROM flux_chat_messages)
ORDER BY id ASC

Also probably you could have

SELECT Name, Message
FROM flux_chat_messages
ORDER BY id DESC
LIMIT 5

and reverse the results in your program (or use another subquery for that)