1

I have data in the following format :

AC-1234/12
AC-4321/12
ANA-4532/12
231
IHC-543/12
F-125/12
F-345/12
ds-234/12

I want to do a natural sort on this in mysql.

the result should be as such :

AC-1234/12
AC-4321/12
ANA-4532/12
F-125/12
F-345/12
ds-234/12
231

tried some of the mysql natural sorting tricks but didn't work for me.Suggestions would be greatly appreciated.

I tried this :

select column 
from table_name 
Order By LENGTH(column), column
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Saurabh Singh
  • 31
  • 2
  • 9
  • 4
    What do you mean by natural sort?? – Mahmoud Gamal Sep 27 '12 at 11:22
  • 1
    Natural join has nothing to do with natural sorting. – lanzz Sep 27 '12 at 11:26
  • i meant natural sorting sorry – Saurabh Singh Sep 27 '12 at 11:27
  • Try it this way: `ORDER BY column ASC` – hjpotter92 Sep 27 '12 at 11:31
  • 2
    Why would a sort allow `F-345/12` to be above `ds-234/12`? – hjpotter92 Sep 27 '12 at 11:32
  • @GiantofaLannister I guess lower case would come after uppercase. – Clodoaldo Neto Sep 27 '12 at 11:56
  • In the results, the line 'IHC-543/12' does not appear, error on copy/paste I guess? – Yaroslav Sep 27 '12 at 12:32
  • Check this SO question [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) and its answers, can bring some light into your question. On MySQL natural sort seems tricky to get using plain sql and must rely on either functions or just adding a rank column on your table. – Yaroslav Sep 27 '12 at 12:43
  • Whhaa...Just found this other link about natural sorting.. ROLF... [CodingHorror: Sorting for Humans : Natural Sort Order](http://www.codinghorror.com/blog/2007/12/sorting-for-humans-natural-sort-order.html) – Yaroslav Sep 27 '12 at 12:53
  • non of that seems to works. Lets say there are two more records AC-100/12 and AC-12/12 then AC-100/12 come first. This is what i got using the sql fiddle shown below. – Saurabh Singh Sep 27 '12 at 13:06
  • sorting these strings numerically as well is required – Saurabh Singh Sep 27 '12 at 13:16
  • As commented before, natural sort is tricky, your best option is to add a column to use it as rank on a `ORDER BY` clause. A `CREATED_ON` column will do the trick too. If not, then you must rely on functions as you can see on the answers to the SO question I linked on my 2nd comment. – Yaroslav Sep 27 '12 at 13:32

2 Answers2

2

You can use the CASE expression to do this like so:

SELECT column
FROM  Tablenametemp 
ORDER BY
   CASE 
     WHEN column REGEXP '^-?[0-9]+$' THEN 1 --If column is number 
     ELSE 0 
   END, column

Here is a SQL fiddle demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
0

i have similar problems, will you guys check mine ?

MySQL Order By Alpha Numeric Not Working

and my sql fiddle

http://www.sqlfiddle.com/#!9/7773a6/1

Viko Wijaya
  • 21
  • 5