Incorrect usage of UNION and ORDER BY?

Question

how can i use union and order by in mysql ?

select * from _member_facebook 
inner join _member_pts 
ON _member_facebook._fb_owner=_member_pts._username 
where _member_facebook._promote_point = 9 
ORDER BY RAND() limit 2 
UNION ALL
select * from _member_facebook 
inner join _member_pts 
ON _member_facebook._fb_owner=_member_pts._username 
where _member_facebook._promote_point = 8 limit 3

give me error

#1221 - Incorrect usage of UNION and ORDER BY

any one can help ?

score 95 · Accepted Answer · edited Nov 28 '14 at 23:50

Try with:

(
  select 
    * 
  from 
     _member_facebook 
   inner join 
     _member_pts 
   ON 
     _member_facebook._fb_owner=_member_pts._username 
  where 
    _member_facebook._promote_point = 9 
  ORDER BY RAND() 
  limit 2
) 
UNION ALL
(
  select 
    * 
  from 
    _member_facebook 
   inner join 
    _member_pts 
   ON 
     _member_facebook._fb_owner=_member_pts._username 
  where 
    _member_facebook._promote_point = 8 
  limit 3
)

Although, I think you should put the ORDER BY clause at the end of the second query

Álvaro González · Answer 2 · 2015-07-13T16:18:15.657

With parenthesis:

(
    SELECT *
    FROM _member_facebook
    INNER JOIN _member_pts
    ON _member_facebook._fb_owner         =_member_pts._username
    WHERE _MEMBER_FACEBOOK._PROMOTE_POINT = 9
    ORDER BY RAND()
    LIMIT 2
)
UNION ALL
(
    SELECT *
    FROM _MEMBER_FACEBOOK
    INNER JOIN _MEMBER_PTS
    ON _MEMBER_FACEBOOK._FB_OWNER         =_MEMBER_PTS._USERNAME
    WHERE _MEMBER_FACEBOOK._PROMOTE_POINT = 8
    LIMIT 3
)

Said that, it isn't mandatory for MySQL to keep the inner sorting in the outer clause—though it'll probably do so since it needs to sort rows anyway to calculate the corresponding LIMIT clauses.

yeah, I was missing the parenthesis too, and adding it fixed it — Tomás, Jun 07 '17 at 07:54

score 30 · Answer 3 · edited May 23 '17 at 12:34

Explanation:

It's important to understand how this works to avoid "gotchas" in similar use cases. Note thatunion's syntax is somewhat "special":

_substatement union all _substatement union all _substatement [order by-clause] [limit-clause]

where "substatement" can optionally be surrounded by ( and ). Some working examples:

select 1 union all (select 2);
select 1 union all  select 2  union all (select 3);
select 1 union all (select 2) union all  select 3;
select 1 union all (select 2) union all (select 3);
select 1 union all (select 2) union all (select 3) union all  select 4;
select 1 union all (select 2) union all  select 3  union all (select 4);

However, if you surround the first "substatement" with braces, you must surround all the other "substatement"s with braces:

(select 1) union all (select 2) union all (select 3);

(Note that the above point is not mentioned in the official docs.)

Failing to do that is a syntax error:

mysql> (select 1) union all select 2; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error in your SQL syntax; check the...
mysql> (select 1) union all (select 2) union all  select 3; -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...
mysql> (select 1) union all  select 2  union all (select 3); -- error because not all "substatement"s are braced
ERROR 1064 (42000): You have an error...

Next, each "substatement" can contain where, group by, having, join, limit, but not order by.

If you'd like to use order by, the "substatement" that contains order by must be surrounded by braces. (Which means they are no longer optional.)

Now, if we'd look at the syntax again:

_substatement union all _substatement union all _substatement [order by-clause] [limit-clause]

we can see that the entire union statement ends with an optional order by / limit. These two keywords apply to the entire union statement, not just the last "substatement":

mysql> select 1
    -> union all
    -> select 2 limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

We've mentioned previously that the limit keyword can also be applied to individual "substatement"s:

mysql> select 1 limit 1
    -> union all
    -> select 2;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

If you want to apply limit to the last "substatement" (as opposed to the entire union statement), you must surround the last "substatement" with braces:

mysql> select 1
    -> union all
    -> (select 2 limit 1);
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

To apply limit to the the last "substatement" and also to the entire union statement, use:

mysql> select 1
    -> union all
    -> (select 2 limit 1)limit 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

It's the same with order by:

mysql> select 1
    -> union all
    -> (select 2 order by 1)order by 1;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql>

But note that applying order by to "substatement"s is meaningless because the docs have explicitly stated that order by is only guaranteed (cf.) to work when applied to the entire union statement:

&puncsp;–§– ..use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result..

The only way order by would make sense in a "substatement" is if you combine it with limit:

&puncsp;–§– ..the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result.

Also, if you want to combine select into with union, there'll be more "gotchas" to watch out for. See issue 32858 regarding this.

score 2 · Answer 4 · answered May 31 '20 at 23:09

Using parentheses fixed my problem while using Order by and limit clauses in the query. My requirement was to get the top and the bottom row in the table with a certain condition and the following code worked for me:

(SELECT column1, column2
FROM table1
ORDER BY column1, column2
LIMIT 1)

UNION

(SELECT column1, column2
FROM table2
ORDER BY column1, column2 
LIMIT 1)

score 0 · Answer 5 · edited May 24 '16 at 05:10

0

The correct is:

(SELECT *
   FROM _member_facebook
   INNER JOIN _member_pts ON _member_facebook._fb_owner=_member_pts._username
   WHERE _member_facebook._promote_point = 9 LIMIT 2)
UNION ALL
  (SELECT *
   FROM _member_facebook
   INNER JOIN _member_pts ON _member_facebook._fb_owner=_member_pts._username
   WHERE _member_facebook._promote_point = 8 LIMIT 3)
ORDER BY 1

edited May 24 '16 at 05:10

ρяσѕρєя K

132,198
53
198
213

answered May 24 '16 at 04:49

Diego Vintimilla Espinoza

1

score 0 · Answer 6 · edited Sep 04 '18 at 06:19

0

try () i think like

(SELECT  CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1) 
UNION ALL
(SELECT  CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1);

edited Sep 04 '18 at 06:19

andreas

16,357
12
72
76

answered Sep 04 '18 at 05:57

anil kumar meena

51
6

score 0 · Answer 7 · answered Oct 12 '18 at 21:44

0

I think use of parenthesis is mandatory if you use order by or limit or both. I tried working on a query by using limit and order by interchangeably without parenthesis and the query did not work. It only worked after adding parenthesis.

answered Oct 12 '18 at 21:44

Syed Quader

1
2

Incorrect usage of UNION and ORDER BY?

7 Answers7

Explanation:

Linked