xhx44w
Last Updated: February 25, 2016
·
1.644K
· filipekiss
1900150 10203074354438338 1015529188 n

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!

Say Thanks
Respond

1 Response
Add your response

24532
0 jhxb8kjohmrynwqzjgfh8ffie4dd9aqzbkwh8fd vjjpdel4v67uabgskns1vdbn4igw78cw20zl

Great article.

P.S:
Typo ORDER BY LENGHT(name).
Please correct it.

-Ashok Chakravarthi

over 1 year ago ·