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.


Doug aka Nullvariable 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.

Benjamin said...

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

Anonymous said...

Today,we are proud to announce the launch of the new wedding support service sell ffxi gil,packed with features sure to sell ffxi gils delight adventurers across Vana'diel looking to exchange eternal vows with their beloved!Responding to player demands for greater customization,the new service will grant brides and grooms freedom in choosing location,timing,dialogue,and sell Final Fantasy XI Gil more for their ceremony,allowing them to create a truly memorable event all their own.Information on all the features,including in-game sell ffxi gil item vendors and wedding certificates,can be found on the new wedding support site,so head on over sell ffxi gils and get started planning the wedding of your dreams sell Final Fantasy XIGil!

qishaya said...

Milan create week ended last night with donatella christian louboutin london disregard presenting the ending show of the autumn christian louboutin online frost 2003 italian collections.Donatella, the creative chief of christian boots the house her delayed brother founded, delivered a collection that was, christian louboutin uk as they say, very christian louboutin shoes.First up was the versus limit, louboutin boots the cheaper christian louboutin line. Girls stomped christian louboutin 2010 out with backcombed tresses bearing turquoise leather trousers, christian louboutin uk sale patchwork pullover and blonde fur sliced into stoles and active jackets. christian boots uk But this aggressive hell’s angels look almost seemed a caricature of the christian louboutin boots christian louboutin boots comfort. louboutin sandals Next up was gianni christian louboutin, buy christian louboutin the main collection

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