CODEWITHSUNDEEP
phpmysqlnatural-sort
Digital Craft Studios
Digital Craft Studios

Reputation: 230

php natural order sorting of mysql select rows

I'm running a select that returns alphanumeric results, e.g:

ABC-1
ABC-2
ABC-10
SAM-1
SAM-2
SAM-10
SAM-20

I've tried using:

ORDER BY CAST(mid(field_name, 6, LENGTH(class) -5) AS unsigned)

and 

ORDER BY filed_name + 0 ASC

this has helped put some order but I cant seem to order -2 before -10

many thanks

Upvotes: 0

Views: 701

Answers (1)

Tomalak
Tomalak

Reputation: 338326

How about

ORDER BY 
  LEFT(field_name, INSTR(field_name, '-') - 1),
  CAST(
    SUBSTRING(field_name, INSTR(field_name, '-') + 1) AS INTEGER
  )

Upvotes: 1

Related Questions