1250 - The table "sub" from one of the SELECT cannot be used in the global ORDER clause

Question

I am trying to make a chat site for educational purposes, so during this I want the last 30 messages to appear in ascending order wrt time. For example, the latest posts below, the oldest above. After a huge amount of googling and not finding a solution that could help, I had to ask about it.

This is a MySQL statement.

It returns the data I want, but in descending order. That is, the latter are at the top. Even if I changed ASC to DESC, nothing will happen.

SELECT * FROM (SELECT msg,sender FROM chatlogs WHERE user1='userone' AND user2='usertwo' ORDER BY 'timeofmsg' DESC LIMIT 30) sub ORDER BY 'sub.timeofmsg' ASC

After many tests and attempts to solve, I found out that when I try to sort the resulting table using the PHPMyAdmin UI, it produces the following error. Turns out this is a MySQL bug. So how do I do this?

1250 - The table "sub" from one of the SELECT cannot be used in the global ORDER clause

If you can tell me how to print the request in reverse order, even that will help. But no matter how you help, please explain how your solution will work ... I start on this.

Answer 1

Your first problem is that you are trying to do "order by" in a column that is not in the "sub" table. You will need to return it to an alias:

SELECT * FROM (SELECT msg,sender, timeofmsg  FROM chatlogs WHERE user1='userone' AND user2='usertwo' ORDER BY timeofmsg DESC LIMIT 30) sub ORDER BY sub.timeofmsg ASC

Answer 2

Request error, you cannot use the request because you mentioned that this is not possible.