"Cannot used in global ORDER clause" with mysql ordering

Question

I have 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'm getting:

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

any advice?

Thanks

Answer 1

The reason it doesn't work is the outer ORDER BY can't "see" tb1 - it sees the results of the inner subquery. So in a syntactically correct version of your query, you would 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 are pointing out, this can more simply written as a single query 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 one order by clause