1250 - Table 'sub' from one of the SELECTs cannot be used in global ORDER clause

Question

i'm trying to make a chat site for learning purposes, so in the course of that, I want the last 30 messages appear in ascending order w.r.t. time. Like, latest messages at the bottom, oldest one at the top. After a huge amount of googling and yet having found no solution that could help, I had to ask this.

This is the MySQL statement.

It returns the data I want but in descending order. That is, latest ones at the top. Even if I change the ASC to DESC, nothing happens.

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 a lot of testing and trying solutions out, I found out by myself that when I try to sort the resultant table using PHPMyAdmin's UI, it throws the following error. Turns out it's a MySQL bug of sorts. So how to I work my way around it?

1250 - Table 'sub' from one of the SELECTs cannot be used in global ORDER clause

If you can tell me how to print the query in reverse, even that would help. But no matter how you help, please do explain how your solution would work... I'm a beginner at this.

Answer 1

Your first issue is that you're trying to do an "order by" on a column that is not present in the table "sub". You'll need to return it in the 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