mysql order by numeric column separating positive and negative values -

i want list positive values of weight column ordered weight in ascending order, followed negative values in whatever order.

this i've tried:

select * `mytable` weight >= 0 order weight  union select * `mytable` weight < 0 order weight 

and got:

sql error (1221): incorrect usage of union , order by

just use single query , appropriate order by:

select * `mytable` order (weight >= 0) desc,          weight; 

mysql treats booleans in numeric context number, expression (weight >= 0) treated "1" (for positive) , "0" (for negative).

you cannot depend on ordering of union query, in situation, because removal of duplicates can ordering. shouldn't depend on ordering union all, because nothing in sql mandates rows first subquery returned before rows second (although in practice true).

as note: error getting caused first order by, not second.