Incorrect usage of UNION and ORDER BY?

user_454229 0
Viewed 48044

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 ?

union sql-order-by mysql
7 Answers

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

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);
user_103132240

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.

user_104973180
Related