How can we order a union query in MySQL?

Question

I have a stored procedure like below in MYSQL:

    (SELECT 1 AS sort_col, col_a, col_b FROM t1 WHERE col_b LIKE %some% AND col_b LIKE %thing%)
    UNION
    (SELECT 2, col_a, col_b FROM t1 WHERE col_b LIKE %some% OR col_b LIKE %thing%)
 ORDER BY sort_col, col_a;

I want the result of first select show at first, but some results of the first are common in second too, and second select changed their sort_col to 2.(I can't use LIMIT in my selects) what can I do for solve it?

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

Use a sub-query:

SELECT * FROM
(
   (SELECT 1 AS sort_col, col1a, col1b FROM t1)
   UNION
   (SELECT 2, col2a, col2b FROM t2)
) tbl 
ORDER BY sort_col, col1a;

Edit (after OP's edit)

SELECT * FROM
(
  (SELECT 1 AS sort_col, col_a, col_b 
     FROM t1 
    WHERE col_b LIKE %some% 
      AND col_b LIKE %thing%)
  UNION
  (SELECT 2, col_a, col_b 
     FROM t1 
    WHERE col_b LIKE %some% 
       OR col_b LIKE %thing%)
) tbl
ORDER BY sort_col, col_a;

edited May 23 '17 at 10:32

Community

1
1

answered Jul 24 '13 at 09:48

Himanshu Jansari

30,115
28
106
129

tnx but it doesn't work! – Fatemeh Gharri Jul 24 '13 at 10:08
@Highlan - This is how it should work. Please show your sample data and expected result. – Himanshu Jansari Jul 24 '13 at 10:09
Please look at my edited question again. – Fatemeh Gharri Jul 24 '13 at 10:25
@Highlan - See my updated answer. – Himanshu Jansari Jul 24 '13 at 10:28
Because some results in first select in common in the second too, the sort_col doesn't properly work! – Fatemeh Gharri Jul 24 '13 at 10:39
@Highlan - I think you are missing something there is no logic to use SELECT * FROM (first select statement) UNION SELECT * FROM (Second SELECT). Both will result same. http://sqlfiddle.com/#!3/1f502/7 – Himanshu Jansari Jul 24 '13 at 10:55
maybe, actually i have an ORDER BY at the end of each select. sorry, i forgot that – Fatemeh Gharri Jul 24 '13 at 11:24
@Highlan - Is your problem solved or not? – Himanshu Jansari Aug 08 '13 at 09:32

How can we order a union query in MySQL?

1 Answers1