Incorrect usage of UNION and ORDER BY?

Solution 1:

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

Solution 2:

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.

Solution 3:

Explanation:

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

_substatementunion all _substatementunion 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:

_substatementunion all _substatementunion 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.

Solution 4:

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)