CODEWITHSUNDEEP
mysqlsqlsql-order-by
Laurent Crivello
Laurent Crivello

Reputation: 3931

ORDER BY separately positive & negative numbers in MySQL statement

I have a MySQL table and would like to return the rows based on a column value in this order:

E.g. 0,2,4,7,-2,-3,-5

Upvotes: 5

Views: 8070

Answers (2)

Bort
Bort

Reputation: 7638

Can use SIGN to sort the positive numbers to the top, then take the absolute value with ABS to get the desired ASC/DESC.

SELECT * FROM theTable
ORDER BY SIGN(col) DESC, ABS(col)

EDIT

As Nahuel pointed out, the above will sort 0's to the middle between positive and negative. To instead group them with the positives, you can use a CASE instead (or, if your column is only integers, the slightly magical SIGN(col + 1))

SELECT * FROM theTable
ORDER BY 
    CASE WHEN col >= 0 THEN 1 ELSE 2 END,
    ABS(col)

Upvotes: 15

DonCallisto
DonCallisto

Reputation: 29932

SELECT columnName1 FROM Tbl
WHERE columnName1 >= 0
ORDER BY columnName1 ASC

UNION

SELECT columnName1 FROM Tbl
WHERE columnName1 < 0
ORDER BY columnName1 DESC

Should work

Upvotes: 2

Related Questions