ORDER BY FIELD clause at the end of complicated mysql query with more than one UNION clouse

Question

I have read carefully this one: How to use ORDER BY inside UNION

Despite this, ORDER BY clause does not work on this complicated query:

SELECT *, 'tbl' as src   FROM
(

SELECT name3, prec3, name6, prec6  
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6  
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 != '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 != '999' 
    AND (name6 REGEXP 'Shabla|Kaliakra|Varna' )      
    GROUP BY name3

UNION

SELECT name3, prec3, name6, prec6  
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 != '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 = '999' 
    AND (name3 REGEXP 'Shabla|Kaliakra|Varna')      
    GROUP BY name3

UNION

    SELECT name3, prec3, name6, prec6 
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 = '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 != '999' 
AND (name6 REGEXP 'Shabla|Kaliakra|Varna')      
   GROUP BY name6
) tbl


UNION


SELECT *, 'tbl' as src   FROM
(

SELECT name3, prec3, name6, prec6    
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 != '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 != '999' 
    AND (name6 REGEXP 'N.selo|Vidin|Vratca')   GROUP BY name3

UNION

SELECT name3, prec3, name6, prec6  
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 != '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 = '999' 
    AND (name3 REGEXP 'N.selo|Vidin|Vratca')      
GROUP BY name6

UNION

SELECT name3, prec3, name6, prec6   
FROM `prec3_2015` p3
    JOIN `prec6_2015` p6
    ON p3.m_d_t = p6.m_d_t AND name3 = name6
WHERE prec3 = '-' AND p3.time = 12 and p3.date = 24 and p3.month = 1 AND prec6 != '999' 
AND (name6 REGEXP 'N.selo|Vidin|Vratca')      
GROUP BY name6
) tbl

ORDER BY FIELD (src, 'Shabla|Kaliakra|Varna|N.selo|Vidin|Vratca')

The two tables are near identical and have 7 columns - id, month, date, time, m_d_t, name3, prec3 or name6, prec6 for the second table. I can't understand how to order result in a way described at the end of the query? Thank you in advanced!

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

ORDER BY FIELD should be like,

ORDER BY FIELD (src, 'Shabla', 'Kaliakra', 'Varna', 'N.selo', 'Vidin', 'Vratca')

You should provide values seperated by , not like regular expression with |

Reference: ORDER BY FIELD in specific order

edited May 23 '17 at 12:06

Community

1
1

answered Jan 30 '15 at 10:02

Sridhar DD

1,972
1
10
17

While this remark about `ORDER BY FIELD` is entirely correct, it is not enough to answer the question because the query in the question always selects `'tbl'` in column `src`. – axiac Jan 30 '15 at 10:09
@axiac :: We can use alias in ORDER BY. check here http://stackoverflow.com/questions/14109076/order-by-alias-in-mysql-contained-in-if-query – Sridhar DD Jan 30 '15 at 10:20
Though `src` can be used in `order by`, example in the main question is not correct. Value for `src` is always `'tbl'` and can't be `'Shabla', etc...` – Ravinder Reddy Jan 30 '15 at 10:25
@SridharDD Read Ravinder's comment above. Didn't I say the same thing? Your answer is correct, no question about `src` or `ORDER BY FIELD()`. But the original query never returns `'Shabla'` in `src`. This is why, with our without your answer, it still doesn't achieve their goal. – axiac Jan 30 '15 at 10:37

axiac · Answer 2 · 2015-01-31T15:24:31.550

0

Try something like this:

SELECT name3, prec3, name6, prec6, name6 AS src  
...

UNION

SELECT name3, prec3, name6, prec6, name3 AS src  
...

...

UNION

SELECT name3, prec3, name6, prec6, name6 AS src  
...

ORDER BY FIELD (src, 'Shabla', 'Kaliakra', 'Varna', 'N.selo', 'Vidin', 'Vratca')

Remove the outer query.

Add to the SELECT clause of each query a new expression X AS src where X is 1, 2, 3 and so on, a different value for each query.

The ORDER BY clause after the last query applies at the UNION level; there is no need to use parenthesis. However, if you want to make it more clear you can enclose each query in parenthesis and left the UNIONs and ORDER BY outside parenthesis.

You will get the rows returned by the first SELECT followed by those returned by the second SELECT and so on. However, the rows returned by each select will be in no particular order (and this is how it looks like you need them).

edited Jan 31 '15 at 15:24

answered Jan 30 '15 at 10:07

axiac

68,258
9
99
134

I corrected the delimiter in the "ORDER BY" clause, it was stupid copy/paste error. Removed the outer query and put different aliases on every "SELECT" clause - exactly as in the advice above, but the result is the same. There is not the order I wish. May be there is no way to do this in that case of query? – mmirro Jan 31 '15 at 14:55
Oh, now I understand how do you want to sort them and I modified the query in the answer. Put `name6 AS src` or `name3 AS src` (depending on what field of each table/SELECT contains the values you want to sort by) in the `SELECT` clause and use the correct usage of function `FIELD()` in the `ORDER BY clause. – axiac Jan 31 '15 at 15:27
Yes! That's it! name3 as src, name6 as src.. Thank you, thank you vary mach! The problem is solved. – mmirro Jan 31 '15 at 17:03

ORDER BY FIELD clause at the end of complicated mysql query with more than one UNION clouse

2 Answers2