Thursday, October 12, 2006

mySQL natural number ordering

Had a situation today where I needed to order a list that looked like "DISTRICT 1, DISTRICT 2...DISTRICT 10, DISTRICT 11". Ordering this field with a simple ORDER BY groupField ASC puts "DISTRICT 10" and "DISTRICT 11" before "DISTRICT 2", which is not what I wanted. Also, the field wasn't always going to be setup as single string, a space, and a number, so I couldn't just SUBSTRING out the trailing number. Found the solution in the mySQL forums:

ORDER BY LENGTH(groupField) ASC, groupField ASC

now the ordering is as you'd expect from a user standpoint.

3 comments:

Anonymous said...

Thanks for posting this! It took me several Google searches to find it but it works perfectly now. I'm not sure why the length is an issue with getting MySQL to sort strings with a natural number order but at least this does the trick. I had a series of postcodes where you can't add a 0 in front of the 1 etc.

Unknown said...

thanks a bunch for this, sorted my problem very quickly, I also had a problem with sorting postcodes naturally, thanks a lot!

jaring futsal said...


The article posted was very informative and useful
thanks for sharing..
jaring futsal | jaring golf | jaring kassa / jaring polynet | jaring pengaman proyek | jaring pengaman bangunan | jaring pengaman gedung | jaring gawang | jaring paranet / jaring tanaman | rumput sintetis / rumput futsal |
tangga darurat | jaring cargo | agen jaring | jaring outbound | jaring truk | tali tambang
http://jaringfutsal.wordpress.com
http://jualjaringfutsal.wordpress.com
http://tokojaring.wordpress.com
http://jualtambangmurah.wordpress.com
http://pasangjaringfutsal.wordpress.com
http://pancasamudera.wordpress.com