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.