CODEWITHSUNDEEP
mysqlsql-order-by
NotGaeL
NotGaeL

Reputation: 8484

mysql order by numeric column separating positive and negative values

I want to list positive values of the weight column ordered by weight in ascending order, followed by negative values in whatever order.

This is what I've tried:

SELECT *
FROM `mytable`
WHERE weight >= 0
ORDER BY weight 
UNION
SELECT *
FROM `mytable`
WHERE weight < 0
ORDER BY weight

And I got:

SQL Error (1221): Incorrect usage of UNION and ORDER BY

Upvotes: 0

Views: 681

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Just use a single query and then an appropriate order by:

SELECT *
FROM `mytable`
ORDER BY (weight >= 0) desc,
         weight;

MySQL treats booleans in a numeric context as a number, so the expression (weight >= 0) is treated as "1" (for positive) and "0" (for negative).

You cannot depend on the ordering of a union query, in any situation, because the removal of duplicates can do anything to the ordering. You shouldn't depend on the ordering with a union all, because nothing in SQL mandates that rows from the first subquery are returned before rows from the second (although in practice this is true).

As a note: the error you are getting is caused by the first order by, not the second.

Upvotes: 1

Related Questions