0

table Picture with rows have name 

1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg
select * from Picture order by name

mysql order : 1.jpg,10.jpg,11.jpg,2.jpg,3.jpg,......

Issue:

I want it sort all type name like as with Window 1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg

and it must working with other case as 

flower01.jpg,flower02.jpg,flower031.jpg,....,flower10.jpg

please help me

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
meotimdihia
  • 4,191
  • 15
  • 49
  • 69
  • It uses alphabetical sort order. If you think about it, mysql is doing the right thing. – Klaus Byskov Pedersen Oct 21 '10 at 17:01
  • @klaus yeah, but it's still not really desirable in this case. There doesn't seem to be an easy solution for this in mySQL. I'm surprised! – Pekka Oct 21 '10 at 17:02
  • 1
    possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) – Pekka Oct 21 '10 at 17:02
  • 1
    Lots more, maybe one contains a solution, I'm too lazy to sift through them: http://stackoverflow.com/search?q=mysql+natural+sort – Pekka Oct 21 '10 at 17:03

3 Answers3

2

You basically have 4 choices.

  1. Change name to a numeric type, and remove the .jpg (adding it later in code)
  2. Add an 'order' column and sort by that
  3. Sort it in your code, not in SQL
  4. Use of the of the cast 'hacks' (e.g. CAST(NAME AS UNSIGNED)
Matthew Smith
  • 1,287
  • 1
  • 9
  • 19
1

A hackish way to do it:

... ORDER BY CAST(name AS UNSIGNED);

Edit: an alternative would be:

 ... ORDER BY LPAD(name,N,'0');

Where N is the maximum width of your column. Sadly, MySQL doesn't support natural sorting natively AFAIK.

Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • In all cases you mentioned: yes. In all cases where natural sorting could be handy: indeed no. See [the link Pekka provided](http://stackoverflow.com/search?q=mysql+natural+sort) – Wrikken Oct 26 '10 at 08:10
0

ODER BY name sees that name is a string and sorts it accordingly,charater by character. You have to make MySQL interpret the name as numeric value. A way might be something like this:

select * from Picture order by name * 1;
johannes
  • 15,807
  • 3
  • 44
  • 57
  • I find an explicit `CAST` preferable to this because of long-term maintenance: Somebody looking at the code in five years will not understand what the hell this was for :) – Pekka Oct 21 '10 at 17:08