Last Updated: February 25, 2016
· filipekiss

Natural Sorting in MySQL

DISCLAIMER: If this was already posted, I'm sorry, but since we don't have a 'search' feature for protips, I'd rather make a repost than keep this away from you, guys :)

So, I was messing with some tables this weekend, and I needed to sort a query using natural selection.

Let's assume we have the following collection of data

Sample data

So we just go on MySQL and run the following query

SELECT * FROM testdata ORDER BY name</code>

The result is the data sorted as string:

Wrongly Sorted data

To prevent this problem, we may ask MySQL to sort the data by LENGTH first and them by name, like this:

SELECT * FROM testdata ORDER BY LENGHT(name), name </code>

Great! Now it works. The skies are blue once again.

Correctly sorted data

Let's just reverse the order and... Oh, shit. If you need to reverse the order, and you ran
SELECT * FROM testdata ORDER BY LENGHT(name), name DESC</code> you'd be surprised with the following result

Not so correctly sorted data

But, once again, this is easy to solve; Just make sure you pass the DESC argument to both of the ORDER BY filters, like this

SELECT * FROM testdata ORDER BY LENGHT(name) DESC, name DESC</code>

And there you have it.

Finally, correctly sorted data!

1 Response
Add your response


Great article.

Please correct it.

-Ashok Chakravarthi

over 1 year ago ·