Hold on... This isn't EnnuiDesign.com — What Gives?
It's been a long time coming, but Jason Lengstorf, formerly of Ennui Design, has expanded his team to include Drew Douglass, Rob MacKay, Henry Moran, and Tom Sturge.
It didn't feel right to keep the same name, so we decided to continue on as Copter Labs. You can expect the same great content under this new name!
Natural Sorting in MySQL
Working Around MySQL's Lack of Built-In Support for Natural Sorting
Several times in the last few projects I've built, I've come across the need to sort alphanumeric rows in a database by number (i.e. entry1, entry2, entry3, etc.). If you've ever tried to do this, you know that sorting entries that are alphanumeric is a pain.
The Data
For our purposes, we'll be using the following table:
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test2 | 2 | | test3 | 3 | | test4 | 4 | | test5 | 5 | | test6 | 6 | | test7 | 7 | | test8 | 8 | | test9 | 9 | | test10 | 10 | | test11 | 11 | | test12 | 12 | -------------------------- -------------
The Problem
Sorting by the column integer, of course, presents no problems.
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY integer
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
-------------------------- -------------
However, when we try sorting by the alphanumeric column, we get unexpected results:
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY alphanumeric
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
-------------------------- -------------
Obviously, this is not the desired outcome. Since we're sorting alphabetically, the entries are actually in the correct order, but we need to find a way to sort numerically.
The Solution
There are a whole lot of solutions out there if you hit up Google for "natural sorting in MySQL." The one I've found to be the most straightforward is to sort by length first, then the column value.
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
-------------------------- -------------
This works because the first nine entries are 5 characters long, with the tenth, eleventh, and twelfth entries at 6 characters; this creates two groups of entries that are then sub-sorted, resulting in our desired order.
Summary
This is a quick tip, but a useful one. Do you have a better way of forcing natural sorting in MySQL? Let me know in the comments!
Jason Lengstorf
Jason Lengstorf a turbogeek hailing from Portland, Oregon. He designs and develops websites using PHP, MySQL, JavaScript (jQuery), CSS, and HTML. He's written two books (PHP for Absolute Beginners [2009 Apress] and Pro PHP and jQuery [2010 Apress]), and he's written articles on development and design for Nettuts, CSS Tricks, and Smashing Magazine, among others.
Comments.
well, i try ask my member in
http://www.php.net.my/forum/abs-mysql
but they give me order option
Amin007 9:20am Nov 22, 2009 permalink | flag
The greatest gifts in life are not purchased, but acquired through hard work and determination. Find the star that twinkles in your heart?for you alone are capable of making your brightest dreams come true. Give your hopes everything you've got and you will catch the star that holds your destiny.
Air force nike pas cher 7:22pm Oct 18, 2011 permalink | flag
Take pride in your accomplishments, as they are stepping stones to your dreams. Understand that you may make mistakes, but don't let them discourage you. Value your capabilities and talents for they are what make you truly unique.
Supra Shoes Prix 7:22pm Oct 18, 2011 permalink | flag
Running can be hard on your body. With the Asics Gel Kayano 17 it doesn't have to be. There is tons of gel cushioning to minimize impact and the ride quality of this shoe is all about comfort. A good dose of support helps keep over-pronators in good alignment. If you seek a luxurious ride, that takes the sting out of the road, grab a pair of Kayano 17s and enjoy the run.Model Number: T100N.0193 Important Shipping Note: U.S. sales only.Need fit help? View all colorsWeight:11.9 oz (size 9)Stack Height:Heel (32mm), Forefoot (20mm)How It Fits (based on width D) Width D=Medium,Width 2E=Wide,Width 4E=Extra WideSizing: Standard running shoe lengthHeel: Medium to narrowMid-foot: Medium volumeForefoot: MediumToe-Box height: MediumArch Structure: Medium heightProduct LifecycleRelease date: November 2010Model discontinued: UnknownHeritage: Gel Kayano 16 INTRODUCTIONThe Kayano 17 is a maximum feature, maximum support shoe designed for daily training and high mileage. It is built with a semi-curved shape.CUSHIONINGRearfoot Gel cushioning systemis a large silicone based gel that attenuates shock during impact.Discrete Heel unitis Asics' highest degree of shock absorption unit in the heel and is currently the most advanced component of IGS.Forefoot Gel cushioning systemis a silicone based gel that attenuates shock during toe-off.MIDSOLESolyteis Asics' top of the line cushioned and responsive midsole. Solyte is very light for the level of cushioning and durability it delivers.Guidance Lineis a vertical flex groove component of IGS and is primarily articulated in the forefoot to improve gait efficiency.DuoMax Support Systemis located in the medial midfoot and is a dual density midsole that is designed to reduce excess pronation.Space Trusstic Systemis located at the bottom of the midsole and is made out of thermal plastic. This gender specific midfoot unit controls midsole deformation which allows for natural foot movement while stabilizing the midfoot of the shoe.Impact Guidance System (IGS)is Asics' design philosophy that enhances the foot's natural gait.UPPEROpen meshencompasses the majority of the upper and offers breathability to keep the foot cool.Asics logois located at both the medial and lateral sides of the upper to keep the shoe snug to the foot.Personal Heel Fit (PHF)is a two layer memory foam lining in the heel collar which creates a personalized fit.Asymmetrical Lacing Designbetter follows the shape of the foot for an improved fit experience.ComforDry Socklineris made from an anti-bacterial material that not only reduces odor and moisture but also provides additional cushioning.Solyte 55 Lastis located in the rear portion of the shoe and has a medium density to deliver a moderately soft feel under the foot. Traditional strobel last completes the forefoot of the shoe.OUTSOLEAsics High Abrasion Rubber (AHAR)is located in the heel and is a carbon rubber that offers exceptional durability.DuraSpongeis located in the forefoot and is a blown AHAR compound that delivers cushioning, responsiveness, and flexibility.
Asics Running Shoes 1:41am Oct 28, 2011 permalink | flag
Thanks for posting this informative article This is a really good read for me. Hey this is a great post. I amm going to email this to my buddies. Thanks for posting this informative article I was searching some information.
Ken Griffey Shoes 7:16am Nov 24, 2011 permalink | flag
Amazing write-up! This could aid plenty of people find out more about this particular issue. Are you keen to integrate video clips coupled with these? It would absolutely help out. Your conclusion was spot on and thanks to you; I probably won’t have to describe everything to my pals. I can simply direct them here!
herve leger 8:45pm Nov 27, 2011 permalink | flag
Right, I am fully agree with your post.This kind of evening dress is so perfect.Not only cheap but also beautiful.I like it so much and hope you will like it too!
evening dress 1:20am Nov 29, 2011 permalink | flag
Tiffany is about the world's most renowned designers of first-rate offerings.Dissimilar to other vogue companies,tiffany necklaces deal firmly in products for instance jewelry,designer watches,glass wares,lamps,bags,plus more.
tiffany jewellery 10:57pm Dec 1, 2011 permalink | flag
I wonder how you got so good. This is really a fascinating blog, lots of stuff that I can get into. One thing I just want to say is that your Blog is so perfect!
Ken Griffey Shoes 1:51am Dec 23, 2011 permalink | flag
Blogs are good for every one where we get lots of information for any topics nice job keep it up !!!
Ken Griffey Shoes 1:52am Dec 23, 2011 permalink | flag
Since the start of his campaign, Gingrich has Adidas Titan wagered that a display of humility will convince voters that Adidas Shoes the former House speaker who shut down Jeremy Scott Shop the government and earned headlines with provocative Adidas Porsche rhetoric in the 1990s has matured Jeremy Scott Wings into a presidential contender.With only a week before the first round of voting in Iowa’s Jan.
Adidas Shoes 4:12am Jan 1, 2012 permalink | flag
Thanks for the write up! This is really some great stuff here! Wishing for a following post for the similar subject.
Ken Griffey Shoes 6:01pm Jan 4, 2012 permalink | flag
Sit not sad because that time a fitful aspect wearied
Asics Shoes 1:53am Jan 14, 2012 permalink | flag
dcpwtA [url=http://redlandsliving.com/]penny auctions[/url] allaXls
vakudk penny auctions ZaXcXqc
Aopujc [url=http://paulchristenbury.com/]make money online[/url] QdsacXl
ddhlhi make money online vRXRQRi
maydaypag 4:17am Jan 27, 2012 permalink | flag
Since yves Saint Laurent is accused of cheating ¡ The Lawsuit Between Christian Louboutin plus yves saint LaurentThe latest turmoil between yves Saint Laurent in addition to Christian Louboutin, the earliest ancestor with red treads, continues. Since yves Saint Laurent can be accused of cheating Christian Louboutin is called a European shoe performer who promoted his distinctive luxury women's shoes during early 90s. He proven employing his / her trademark in shiny, red lacquered soles in to his styles in in the near future following year. Consumers should purchase Louboutin's shoes at outlets into the world, too like from net stores in partnership with boutiques; wholesale Christian Louboutin footwear will likely be tailored in some marketplaces. UK's shops are humiliated in NY metropolis, L. A. metropolis, Las Vegas, and moreover Orange County, with an additional ideas for that DesignArea in Miami. Shops also can often be lied in Hong Kong, Newcastle, Australia (Melbourne, Brisbane, along with Sydney), Sao Paulo, South [url=http://www.uklouboutinday.com/]christian louboutin uk[/url] america, and Singapore. Christian Louboutin's length with high conclude footwear was spurred through the bedecked performers he discovered carry out in version French clubs. He was probable making the supreme large your back heel which would embellish women's bodies just by showcasing their legs plus earning them show way up assuming that feasible. Louboutin desires that his merchandise is most likely going to create women of all ages of all ages to take into account by on their very own as icons. His models combine stiletto heels applying heights of 4. seventy two inches width (hundred twenty mm) along with bigger. Louboutin's daring models will be established to defy regulation and help make his clientele feel focused and permitted. Footwear of Louboutin are most typical for currently being dressy plus healthy for evening dress.
That's right. In their view. Tang threChristian Louboutin Shoese whenever since the most suitable partners are still. Tang three smiles to convey: "in this case, we try to traditions clearance it. In our current muscle, should still to, there's a good possibility to land... You have to fix and evaluate to face a sea douro" ZhuZhu QingDao: "the douro ever explained to. Seven of thLouis Vuitton Outlet Just in case you prefer to seek out phenomena wide width footwear out created for dating and also specific sport sneakers for holiday bust, christian louboutin sale footwear or sneakers can match you. That christian louboutin sneakers comfy shoes make it possible energy to [url=http://www.uklouboutinday.com/]christian louboutin uk[/url] make have the entire colour tones, the incredibly chic ness as well as in addition pattern also turn you into feel normally the incredibly best. Christian louboutin footwear boots are resilient fantastic ample therefore you accompaning you simply for several many years. Created for ladies whose perception with design and design will not interact with their shopping shelling out budget, for instance people today a 2nd acknowledged solutions: identifying a web page, because of astounding footwear and boots besides sensible. From net primarily centered, you are going to have out a massive collection of super cheap religious louboutin huge width footwear therefore you complement all your attire devoid of bankrupt. Shoe lovers accompanied by a special eye created pertaining to christian louboutin sale design will find out substantial course sneakers or boots through the use of cyberspace collectively with get them 5 days decreased price tag instead of a originals price amount.
You know, her soul force may be or level of ninety nine. Whether fighting experience or skills to work with, control, and that the sum of the powerful far above the soul force, are not they can certainly easiChristian Louboutin Shoes These people container be located extra Macy'sEvery woman confirms amazing fro buy stately mod increase ons about unite every single styles, more now it will be simple f. Christian Louboutin Boots ro purchase mod designer shoes union more christian louboutin uk change clothes because of expired fro implausible without hold off. If you believe Christian Louboutin could be gather only small major about rich ladies after that consider these thought nevertheless again. Their status am each reason these has give their attractiveness unite they will always each wo ld is apparently at manner. The Christian Louboutin reproduction shoes bring generally been only selection that's simply. tribute more may be inspired beside each almost all glamorous about girls with this report each history due almost every globe. They may be each shoes [url=http://www.uklouboutinday.com/]louboutin[/url] container gird oneself fro each girls belonging fro each planet who wish fro are living their particular lives entirely 'Nothing necessary, I just opened for a workplace properly to feel just beforehand. 'Sui Ge shines through which actually say. 'Is fantastic 'Chen Tai Chong's method, ' is seen, you step into inborn chances are you'll anticipate, should be not concerns. ''Is absolute 'The Sui Ge classmate seems still have no idea of modest christian louboutin united kingdom sale why thing. Chen Tai Chong ha ha on smiling, but don't say Sui Ge particularly conceited.
[url=http://www.uklouboutinday.com/] http://www.uklouboutinday.com/ [/url]
CouppyAbedype 11:26am Feb 3, 2012 permalink | flag
This was a really fantastic post that I really do appreciate. This is something that is really amazing and interesting to me to say the least.
Azelex Cream 12:53am Feb 4, 2012 permalink | flag
Well written article, well researched and useful for me in the future.I am so happy you took the time and effort to make this. See you around
Ken Griffey Jr Shoes 12:17am Feb 7, 2012 permalink | flag
This is really a fascinating blog, lots of stuff that I can get into. One thing I just want to say is that your Blog is so perfect!
Holi Messages 3:27am Feb 22, 2012 permalink | flag
This is something that is really amazing and interesting to me to opinion the least.
holi facebook status 3:28am Mar 1, 2012 permalink | flag
It’s great to determine this information within your post, i experienced been looking exactly the exact but there is not any right resource, many thanks now I possess the web page link which i experienced been searching for my research.%%%
GM Tech 2 12:02am Mar 15, 2012 permalink | flag
Thanks for sharing nice information with us. i like your post and all you share with us is update and quite informative,thanks for sharing………….
Women Lingerie 4:05am Mar 28, 2012 permalink | flag
I always prefer to read the quality content and this thing I found in you post. Thanks for sharing. lyj
Lexia 3 7:28pm Apr 9, 2012 permalink | flag
VAS 5054A Pro: Benz Star C3 Latest Full Version: Transponder Key List: ELM Scan5: VAG 908: MVCI for TOYOTA TIS 100Z
Ford VCM 11:22pm Apr 19, 2012 permalink | flag
these sorts of boots or cannot even load, will really feel discomfort, pain, thumb bursitis, hammer toes too as other deformities. 3. Nike Outlets Heel elevation induced considerable alterations in plantar pressure. within heel elevation increases, the arch using the foot using the difficulty multiplied, developing the difficulty around the forefoot, toes are actually squeezed in in the concept using the toe. 4. once the boots or shoes are unsuitable, low-heeled boots or shoes also result in problems. Will result in difficulty for just about any great offer of numerous years a long-term deformity. Boots several amounts of friction ticket max traditional BW can be exceptionally useful. Women: 1. greatly reduce heel (one inch or much under one certain inch) considerable mind using the boots or shoes would be the very most exceptional choice for women. Relaxed around the top foot toe to increased compliance, this precise same education of heel within evaluation for just as important.
Air Max uk 12:30am Apr 20, 2012 permalink | flag
I have really happy for this website is providing the different pots. I am very much satisfied by this technology and nice approach is visible in this blog. Thanks a lot for sharing something information in this website.
Griffey Shoes 7:20am Apr 24, 2012 permalink | flag
Thanks for getting the time to go over this, I really feel strongly about it and adore knowing additional on this topic. If possible, when you obtain expertise, would you ideas updating your weblog with additional information? It is very beneficial for me.
ebel replica watches 6:28pm Apr 25, 2012 permalink | flag
this trick doesn't work perfectly.
if you have these data as varchar.
c1
c2
c03
c4
c19
use "length(field), field asc" will result.
c1
c2
c4
c03
c19
instead of
c03
c1
c2
c4
c19
vee 9:46pm Apr 26, 2012 permalink | flag
I like it..Thanks for sharing..
Buy Rogaine 3:07am Apr 27, 2012 permalink | flag
If gettable, when you obtain skillfulness, would you ideas updating your weblog with additional accumulation? It is rattling good for me
famous Status 1:58am May 1, 2012 permalink | flag
Join In.
Have something to say? By all means, speak up!
But first, a few rules:
Happy commenting!