in this example :
10-20
20-40
50-60
v
k
r
a
12 month
1 month
how can i sort it in this order ?:
10-20
20-40
50-60
a
k
r
v
1 month
12 month
i use abs(value) but in the alphabetical case doesn't work
in this example :
10-20
20-40
50-60
v
k
r
a
12 month
1 month
how can i sort it in this order ?:
10-20
20-40
50-60
a
k
r
v
1 month
12 month
i use abs(value) but in the alphabetical case doesn't work
OK, thanks to the commenter, now a working version. This sorts on two cases in the order by clause:
select *
from (
select '10-20' as col1
union all select '20-40'
union all select '50-60'
union all select 'v'
union all select 'k'
union all select 'r'
union all select 'a'
union all select '12 month'
union all select '1 month'
) s1
order by
case
when col1 rlike '[0-9][0-9]-[0-9][0-9]' then 1
when col1 rlike '[0-9]+ month' then 3
else 2
end
, case
when col1 rlike '[0-9][0-9]-[0-9][0-9]' then cast(col1 as decimal)
when col1 rlike '[0-9]+ month' then cast(col1 as decimal)
else col1
end
The first case puts categories in order: 00-00 first, then other stuff, and at the end the months. The second case converts the columns to decimal if possible.
If you can get away with doing some processing in PHP, you could use natsort
:
Standard sorting
Array
(
[3] => img1.png
[1] => img10.png
[0] => img12.png
[2] => img2.png
)
Natural order sorting
Array
(
[3] => img1.png
[2] => img2.png
[1] => img10.png
[0] => img12.png
)
Otherwise, there's another question on SO which asks the same thing: http://stackoverflow.com/questions/153633/natural-sort-in-mysql