CODEWITHSUNDEEP
phpmysqlsqlunionsql-order-by
Anita
Anita

Reputation: 159

Can not order by when using UNION in mysql

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 !

Upvotes: 0

Views: 74

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272066

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

Upvotes: 1

ci_
ci_

Reputation: 8774

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

Upvotes: 1

Related Questions