CODEWITHSUNDEEP
mysql
cborgia
cborgia

Reputation: 1419

MySQL order by disregarding signed numbers

I have 10 rows with a column 'passion_level' that has both negative and positive values:

-3 5 8 9 -11 0 3 1 10 -8

If I run the query SELECT * FROM table ORDER BY passion_level DESC I get the numbers ordered as you would expect, from high to low with the negative numbers at the bottom of the list. But how do we get mysql to order a column of signed integers disregarding the sign (treat negative numbers as positive numbers) so we get rows returned in this order:

-11 10 9 8 -8 5 -3 3 1 0

Upvotes: 1

Views: 985

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

SELECT * 
FROM table 
ORDER BY abs(passion_level) DESC

Upvotes: 1

Conrad Lotz
Conrad Lotz

Reputation: 8828

Try and create a new column and use ABS(), then reorder according to the new column desc

SELECT *,ABS(passion_level) as order FROM table ORDER BY ABS(passion_level) DESC

Upvotes: 1

Kermit
Kermit

Reputation: 34063

You can use ABS. It will return the absolute value:

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32
SELECT columnList FROM table ORDER BY ABS(passion_level) DESC

Upvotes: 1

Related Questions