Gunslinger_ Gunslinger_ - 6 months ago 25
MySQL Question

Incorrect usage of UNION and ORDER BY?

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

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