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 ?

Answer 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

Answer 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.