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

Databases
1

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

SELECT *
FROM
 (
SELECT *
FROM table
WHERE .....

) AS this

INNER JOIN .....

UNION 

(SELECT *
FROM some_table
)

order by `population` desc

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

2

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.

3

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.

4

I get Incorrect usage of UNION and ORDER BY

5

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.

6

nope, sorry

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

SELECT foo, bar
  FROM table1
UNION ALL
SELECT qux, fap
  FROM table2
ORDER 
    BY foo
7

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
8

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

9

next time you have trouble with it, please share

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

10

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

11

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

12

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

13

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.

14

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