"Cannot be used in the global ORDER clause" with mysql order

Question

I have a problem with this SQL query:

(SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin FROM users AS tb1, online AS tb2 WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id ORDER BY tb1.publicImage) ORDER BY tb1.id DESC 

for some reason I get:

 Table 'tb1' from one of the SELECTs cannot be used in global ORDER clause 

any advice?

thanks

Answer 1

The reason it does not work is because the external ORDER BY cannot "see" tb1 - it sees the results of the internal subquery. Therefore, in the syntactically correct version of your query, you simply ORDER BY id :

 (SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin FROM users AS tb1, online AS tb2 WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id ORDER BY tb1.publicImage) ORDER BY id DESC 

But as others point out, it could be easier written as a single request, ordered by id

Answer 2

If you put parentheses around your select , then the inner table will not be visible outside

 SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin FROM users AS tb1, online AS tb2 WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id ORDER BY tb1.publicImage, tb1.id DESC 

And you can specify multiple columns in a single order by clause

Answer 3

When you close the parentheses after the select statement, your second order by clause becomes invalid. Instead, try combining your column order in a single order by expression, as shown below:

 SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin FROM users AS tb1, online AS tb2 WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id ORDER BY tb1.publicImage, tb1.id DESC