CODEWITHSUNDEEP
mysqlsqlsql-order-by
Navid Abutorab
Navid Abutorab

Reputation: 1787

order by field greater than 0

suppose I've these rows in my database : 

1
0
5
2
0
3
3
5
0
5

I want to sort my fields , I need to return fields with not zero value first and when with zero value , the sorted rows should look like this 

1
5
2
3
3
5
5
0
0
0

I don't whant to sort by number , I want to show 0 values at last ,

I've this code but it sort them by number :

 ORDER BY cASE WHEN num_mojud = 0 THEN num_mojud END ASC,CASE WHEN num_mojud > 0 THEN num_mojud END ASC
or
ORDER BY IF (num_mojud = 0, 9999999, num_mojud )

how can I do so ?

Upvotes: 0

Views: 2140

Answers (3)

GameTag
GameTag

Reputation: 379

you can select num_mojud from a custom table.

First one : SELECT num_mojud FROM tableName WHERE num_mojud > 0 get all number greater than 0 Second one : SELECT num_mojud FROM tableName WHERE num_mojud = 0 all numbers are equals to 0

SELECT num_mojud 
FROM (
    (SELECT num_mojud FROM tableName WHERE num_mojud > 0) 
    UNION
    (SELECT num_mojud FROM tableName WHERE num_mojud = 0)
) as myCustomTable

Upvotes: 0

Othman Mahmoud
Othman Mahmoud

Reputation: 163

select num_mojud
from MyTable
order by case when num_mojud is 0 then 1 else 0 end, num_mojud

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

In MySQL, you can use two keys for a sort. Note that SQL tables represent unordered sets. So, naively, you can put the zeroes first just by sorting by the value (assuming the values are non-negative):

order by num_mojud

That doesn't seem to do what you want. You can put the zeros first and the rest in an arbitrary order by doing:

order by (num_mojud = 0) desc

MySQL treats booleans as numbers, with "1" for true and "0" for false. Hence, the desc on the order by key.

You can put the zeros first the rest in a random order by doing:

order by (num_mojud = 0) desc, rand()

You can put the zeros first and retain the order of the rest if you have a column that has the ordering you want:

order by (num_mojud = 0) desc, id

Upvotes: 3

Related Questions