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
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:
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.
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
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.