Can not order by when using UNION in mysql

Question

I have this query that is working if I do no do UNION:

SELECT t1.offer_id, t1.cpv_id, t1.type,
FROM `offer_cpv` t1
JOIN `offer` t2     ON t1.offer_id = t2.id
JOIN `company` t3   ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid
AND t3.status IN (4, 6)
GROUP BY t1.offer_id
ORDER BY t1.type

Key part is that I am doing ORDER BY t1.type. But if I use UNION (and I have to) my query breaks saying:

Unknown column 't1.type' in 'order clause'

Here are my UNION tries that do not work:

SELECT t1.offer_id, t1.cpv_id, t1.type,
FROM `offer_cpv` t1
JOIN `offer` t2    ON t1.offer_id = t2.id
JOIN `company` t3  ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid
AND t3.status IN (4, 6)
GROUP BY t1.offer_id
ORDER BY t1.type

UNION

SELECT t1.offer_id, t1.cpv_id, t1.type,
FROM `offer_cpv` t1
JOIN `offer` t2      ON t1.offer_id = t2.id
JOIN `company_2` t3  ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid
AND t3.status IN (4, 6)
GROUP BY t1.offer_id

ORDER BY t1.type

And:

SELECT t1.offer_id, t1.cpv_id, t1.type,
FROM `offer_cpv` t1
JOIN `offer` t2    ON t1.offer_id = t2.id
JOIN `company` t3  ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid
AND t3.status IN (4, 6)
GROUP BY t1.offer_id

UNION

SELECT t1.offer_id, t1.cpv_id, t1.type,
FROM `offer_cpv` t1
JOIN `offer` t2      ON t1.offer_id = t2.id
JOIN `company_2` t3  ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid
AND t3.status IN (4, 6)
GROUP BY t1.offer_id

ORDER BY t1.type

Can I somehow do this UNION but order results by type. I am ordering by type because I need to get output like this:

<h2> Results of type 1 from both tables: </h2>
<div> Result 1 from **first** table </div>
<div> Result 2 from first table </div>
<div> Result 1 from **second** table </div>
<div> Result 2 from second table </div>
<h2> Results of type 2 from both tables: </h2>
<div> Result 1 from **first** table </div>
<div> Result 2 from first table </div>
<div> Result 1 from **second** table </div>
<div> Result 2 from second table </div>

Later I will have to add one more column in ORDER BY clause: t1.rank, so I can order first by type then by rank too !

you need an outer query ... `select x.offer_id, x.cpv_id, x.type from ( here is the union query)x order by x.type` — Abhik Chakraborty, Mar 12 '15 at 10:32

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

You can only order on the whole UNION. Remove the first ORDER BY alias both t1.type to something else, like "mytype". Then change the second ORDER BY to ORDER BY mytype.

See here:

SQL Query - Using Order By in UNION

edited May 23 '17 at 11:43

Community

1
1

answered Mar 12 '15 at 10:32

ci_

8,594
10
39
63

I do not understand, can you show code example please ? – Anita Mar 12 '15 at 10:35
This example workded: http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union . Thanks! – Anita Mar 12 '15 at 10:44

Salman A · Accepted Answer · 2015-03-12T10:56:42.733

From the MySQL manual:

This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY. (Alternatively, refer to the column in the ORDER BY using its column position. However, use of column positions is deprecated.)

So basically you need to (i) create alias for the columns that you need to sort in the first query (ii) use the alias in the ORDER BY clause:

SELECT t1.offer_id, t1.cpv_id, t1.type AS type_column --, t1.rank as rank_column
FROM `offer_cpv` t1
JOIN `offer` t2 ON t1.offer_id = t2.id
JOIN `company` t3 ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid AND t3.status IN (4, 6)
GROUP BY t1.offer_id

UNION

SELECT t1.offer_id, t1.cpv_id, t1.type --, t1.rank
FROM `offer_cpv` t1
JOIN `offer` t2 ON t1.offer_id = t2.id
JOIN `company_2` t3 ON t2.cmpid = t3.id
WHERE t1.cpv_id = :cpvid AND t3.status IN (4, 6)
GROUP BY t1.offer_id

ORDER BY type_column --, rank_column

Oh thanks, I used column position, but I see it it is deprecated. — Anita, Mar 12 '15 at 10:45

Can not order by when using UNION in mysql

2 Answers2