Is there an elegant way to have performant, natural sorting in a MySQL database?
For example if I have this data set:
- Final Fantasy
- Final Fantasy 4
- Final Fantasy 10
- Final Fantasy 12
- Final Fantasy 12: Chains of Promathia
- Final Fantasy Adventure
- Final Fantasy Origins
- Final Fantasy Tactics
Any other elegant solution than to split up the games' names into their components
- Title: "Final Fantasy"
- Number: "12"
- Subtitle: "Chains of Promathia"
to make sure that they come out in the right order? (10 after 4, not before 2).
Doing so is a pain in the a** because every now and then there's another game that breaks that mechanism of parsing the game title (e.g. "Warhammer 40,000", "James Bond 007")
MySQL doesn't allow this sort of "natural sorting", so it looks like the best way to get what you're after is to split your data set up as you've described above (separate id field, etc), or failing that, perform a sort based on a non-title element, indexed element in your db (date, inserted id in the db, etc).
Having the db do the sorting for you is almost always going to be quicker than reading large data sets into your programming language of choice and sorting it there, so if you've any control at all over the db schema here, then look at adding easily-sorted fields as described above, it'll save you a lot of hassle and maintenance in the long run.
Requests to add a "natural sort" come up from time to time on the MySQL bugs and discussion forums, and many solutions revolve around stripping out specific parts of your data and casting them for the
ORDER BY
part of the query, e.g.This sort of solution could just about be made to work on your Final Fantasy example above, but isn't particularly flexible and unlikely to extend cleanly to a dataset including, say, "Warhammer 40,000" and "James Bond 007" I'm afraid.
I know this topic is ancient but I think I've found a way to do this:
Scrap that, it sorted the following set incorrectly (It's useless lol):
Final Fantasy 1 Final Fantasy 2 Final Fantasy 5 Final Fantasy 7 Final Fantasy 7: Advent Children Final Fantasy 12 Final Fantasy 112 FF1 FF2
Here is a quick solution:
I've written this function for MSSQL 2000 a while ago:
So, while I know that you have found a satisfactory answer, I was struggling with this problem for awhile, and we'd previously determined that it could not be done reasonably well in SQL and we were going to have to use javascript on a JSON array.
Here's how I solved it just using SQL. Hopefully this is helpful for others:
I had data such as:
I actually didn't "cast" things though I suppose that may also have worked.
I first replaced the parts that were unchanging in the data, in this case "Scene ", and then did a LPAD to line things up. This seems to allow pretty well for the alpha strings to sort properly as well as the numbered ones.
My
ORDER BY
clause looks like:Obviously this doesn't help with the original problem which was not so uniform - but I imagine this would probably work for many other related problems, so putting it out there.
You can also create in a dynamic way the "sort column" :
That way, you can create groups to sort.
In my query, I wanted the '-' in front of everything, then the numbers, then the text. Which could result in something like :
That way you don't have to maintain the sort column in the correct order as you add data. You can also change your sort order depending on what you need.