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

I’ve got this query and I’d like to order results but can’t seem to find a way!

FROM table
WHERE .....

) AS this



FROM some_table

order by `population` desc

P.S. If i get rid of union it works!

That means you don’t have a field called population on one of the sides of the union. A UNION only truly works properly if the rows returned by both sides have the same number of fields, named the same and of the same type. Granted, some of the DBMS try to make them work without it, but YMMV and the results may not be true and accurate.

I am not fetching population in any select fields. I’m using it purely for ordering!

Union and select fields have same number of columns works just fine without order by.

I get Incorrect usage of UNION and ORDER BY

The reason it works without the union is that the field exists in one of the tables that are being joined, so even though you’re not selecting it, the DBMS has access to the field to be able to order by it. In other words, either table or some_table have a field called population.

When you do a UNION however, ONLY those fields that are selected are available to the DBMS. Essentially, the DBMS creates a temp table which contain ONLY the fields that are selected and UNIONed, then returns rows from that temp table.

nope, sorry

names are taken from the first SELECT, so this is quite valid –

SELECT foo, bar
  FROM table1
SELECT qux, fap
  FROM table2
    BY foo    

INNER JOIN .... UNION better have a SELECT where the dots are

also, (SELECT * FROM some_table) is missing its table alias

pro tip: please do not simplify or obfuscate your SQL to the point where the source of the error is not visible

1 Like

It is, but I’ve always had trouble with it. Like I said, YMMV (your mileage may vary)

next time you have trouble with it, please share

i’m certain that everyone’s mileage is the same on this issue

Will do, even though I try to avoid unions like the plague

try to avoid unions?

that’s like making three right turns to try to avoid making a left

unions have their place, and a quite useful one it is, too

They do, but I’ve been burned by people misusing them

As far as posts on the help forums EVERY time I have seen someone using UNION there was always a bad database design behind it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.