Best IT Recipes

Misuse of UNION and ORDER BY?

how can i use union and order 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

tell me about the error

 #1221 - Incorrect usage of UNION and ORDER BY

can anyone help?

+46
mysql sql-order-by union
Jul 18 '11 at 12:10
source share
4 answers

Try:

 ( 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 at the end of the second query

+59
Jul 18 '11 at 12:12
source share

With parentheses:

 ( 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 )

It is said that for MySQL it is not necessary to save the internal sorting in the external section, although this is likely to be so, since to sort the rows it is necessary to sort the corresponding LIMIT clauses.

+27
Jul 18 '11 at 12:12
source share

Explanation:

It is important to understand how this works in order to avoid "gotchas" in such use cases. Note that union syntax is somewhat "special":

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

where "reinforcement" may be further surrounded ( ) . 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 “reinforcement” with braces, you must surround all the other “substations” with braces:

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

(Please note that the above point is not mentioned in official documents .)

Failure to do so 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...

Then each "slice" may contain where , group by , having , join , limit , but not order by .

If you want to use order by , the subtitle containing order by must be surrounded by curly braces. (This means that they are no longer optional.)

Now, if we look at the syntax again:

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

we see that the whole union statement ends with an optional order by / limit . These two keywords apply to the entire union expression, and not just to the last “smudge”:

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

We mentioned earlier that the limit keyword can also be applied to individual s sub-panels:

  •  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 "subtopic" (as opposed to the entire union operator), you must surround the last "subtitle" 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 last subtopic, as well as to the entire union operator, use:

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

Same thing 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 "subatement" s is pointless because the docs explicitly state that order by guaranteed ( cf. ) to work throughout the union statement:

& puncsp; - & sect; - & ensp; .. use order by for individual SELECT does not say anything about the order in which rows appear in the final result.

The only way order by make sense in a "drip" is to combine it with limit :

& puncsp; - & sect; - & ensp; .. using ORDER BY in this context is usually associated with limit , so it is used to define a subset of the selected rows to retrieve for SELECT , even if it does not necessarily affect the order of these lines in the final union result.

Also, if you want to combine select into with union , there will be more “gotchas” to keep track of, see question 32858 regarding this.

+14
May 6 '15 at 11:05
source share

Right:

 (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
0
May 24 '16 at 4:49
source share


More articles:


All Articles