CODEWITHSUNDEEP
phpsqlmysqlnatural-sort
cosy
cosy

Reputation: 582

sql sort numeric then alphabetically

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

Upvotes: 1

Views: 1361

Answers (2)

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 1

nickf
nickf

Reputation: 546503

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: Natural Sort in MySQL

Upvotes: 2

Related Questions