CODEWITHSUNDEEP
phpmysql
user3256212
user3256212

Reputation: 107

Write query for positive value in ascending order and negative value in

I have mysql table that looks like below : enter image description here

In this table there are minus and plus value. I wants all record in dropdown in a format like.

I need to show first as zero than minus value in descending order and positive value in acsending order. So what query should I have to write?

For example : 

0.00
-1
-2
 ...
 ...
 ...
 1
 2
 3
 ...
 ...
 ...

Upvotes: 4

Views: 488

Answers (3)

Aurangzeb
Aurangzeb

Reputation: 1626

select * from sph order by abs( `value` ) asc

simple ;)

Upvotes: 0

maxhb
maxhb

Reputation: 8865

Use 3 querys and join the results together by using union

(select * from sph where value = 0)
union
(select * from sph where value < 0 order by value desc)
union
(select * from sph where value > 0 order by value asc)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

ORDER BY CASE 
            WHEN Value = 0 THEN 0
            WHEN Value < 0 THEN 1 
            ELSE 2
         END ASC,          
         ABS(Value) ASC

The first part of the ORDER BY places 0 value first, followed by negative values, followed by positive values. The second part orders negative values in descending order and positive values in ascending order.

Upvotes: 5

Related Questions