Search code examples
phpmysqlarrayssortingnatural-sort

MySQL/PHP Sort with natural sort

I writing a code to get mysql field values sorted. my filed values are as below

**downloads**
N/A
10

50
30
unlimited
N/A
70
unlimited

those are on mysql table field. i need to sort those assenting and descending like below

Assending
N/A

10
30
50
70
unlimited
unlimited


Desending
unlimited
unlimited
70
50
30
10

N/A

The space is some rows don't have data. i wrote mysql query like below

SELECT * FROM fltable  ORDER BY LENGTH(downloads), downloads DESC

But this not returns correct sort, can anyone help me with this using my sql or php solution. Thank You

Solution 
  • SELECT * FROM fltable  
ORDER BY case when downloads = 'N/A' then 1
              when downloads is null then 2
              when downloads = 'unlimited' then 4
              else 3
         end DESC, 
         downloads * 1 DESC