CODEWITHSUNDEEP
mysqlsqlsorting
mkocansey
mkocansey

Reputation: 67

Specifying two different sort orders for a mysql query

I have a query that sorts a numeric column in descending order. This column (YTD% in attached picture) is a mix of both positive and negative numbers. ORDER BY ytd DESC displays the data with highest positive values first and so on.
I however want to order the column in descending order (so the positive numbers come before the negatives), but with the numbers starting from least positive to highest and least negative to highest negative. So 1.50, 5.10, 7.10 etc.

image showing current sort order

Upvotes: 1

Views: 123

Answers (2)

SQB
SQB

Reputation: 4078

Depending on what you want exactly, you could do

ORDER BY
    SIGN(ytd) DESC,
    ABS(ytd) ASC

to get first all positive numbers from lowest to highest value and then all negative numbers from least negative to most negative, with any possible zeroes in between those.
In other words,

1
3
3
5
6
0
-3
-8
-10

If you want to have the zeroes come before the positive numbers, use

ORDER BY
    SIGN(SIGN(ytd) + 1) DESC,
    ABS(ytd) ASC

which would yield

0
1
3
3
5
6
-3
-8
-10

Should you want the negative and positive values intermingled, sorting just by their absolute value, just use

ORDER BY
    ABS(ytd) ASC,
    ytd DESC

which would yield

0
1
3
-3
3
5
6
-8
-10

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use multiple keys and expressions in the order by:

order by (ytd > 0) desc,  -- put positive numbers first
         ytd asc

Upvotes: 1

Related Questions