Search code examples
mysqlsqlmysql-error-1111

simple SQL query giving Invalid use of group function

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
Solution

  • 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)