How to do natural alpha-numeric sort in MySQL
Published February 1st, 2006 in Ruby on Rails, EducationLooking through the records in the Teacher! database, I realized that some people are using numbers instead of names to represent their students. That’s fine by me; one of my design objectives with Teacher! is to get the application out of the way. That means it doesn’t care how you choose to refer to your students, and it doesn’t force you to do it in any particular way.
So if you want to just use first names, just use last names, just use number or use some other wacky combination, it doesn’t matter. The application doesn’t choke just because you decided to call one of your students R2D2 (hey, whatever floats your boat).
But the numeric student names weren’t sorting correctly in the class lists. Because Teacher! was expecting a text string, not a number, it was sorting by alpha order, not numerically. That’s no good.
So I did a little digging and came up with an answer. You can force a MySQL SELECT statement to sort naturally (first by letters and then by numbers) by doing this:
SELECT names FROM your_table ORDER BY names + 0 ASC
The added “0” forces it to sort the way you want it to, but doesn’t alter your data in any way. Pretty neat!
For those of you using Ruby On Rails, you can define the sort order in your model. So, for example, in Teacher!, I have a Classroom model that has_and_belongs_to_many => :students. So in the classroom.rb model I have this:
class Classroom < ActiveRecord::Base
has_and_belongs_to_many :students,:order => 'firstname + 0 ASC'
...
end
That way my :classroom objects always have students collections that are sorted alphanumerically. Hurray!
<—3a60a642374c64a449e206fa2e7064e1—>
Worked for me, thanks!
What about when you are concatanating fields… eg
select concat(first name, last name) from names order by ???
what do you put in the question marks??
Well, it depends on how you want to order your result. If you want to order by last name, you’d put “order by last_name”. If you wanted to order by the concatenated field, you’d do “SELECT concat(first_name, last_name) as full_name ORDER by full_name. In either case you can add ” 0” to force MySQL to sort naturally.