Advanced List Sorting With MYSQL

Anon

I have a simple question for all you MySQL masters out there.

I need to sort a list of movies by name. But before I sort them, I'd like to take any movies that begin with 'The ' and move 'The ' to the end of the name, so that the names will sort correctly.

EXAMPLE:

UNSORTED LIST:
Armageddon
Star Wars
The Croupier
The Majestic

SORTED LIST
Armageddon
Croupier, The
Majestic, The
Star Wars

How can I do this? I've written two CONCAT statements that do the trimming/adding, but I don't know how to integrate them with each other and with an additional statement so that all the results ('the' or no 'the') appear in a single result set.

Any ideas/tips would be greatly appreciated!!!

Thanks,
Patrick

Anon

lets see what you got already.

Anon

These are the two statements that trim the 'The ' prefix and add a ', The' suffix...

SELECT TRIM(LEADING 'The ' FROM name)
FROM movies

SELECT CONCAT(name, ', The')
FROM movies

I just need to combine them, and then include all of the movies that did NOT match the initial query, and then sort the whole list by name.

Patrick

mithril

How 'bout combining it all into one query? Try this:

SELECT
  IF(
    LOWER(SUBSTRING(name,1,4)) = 'the ',
    CONCAT(SUBSTRING(name,5), ', The'),
    name
 &nbsp😉 AS name
FROM
  movies
ORDER BY
  name

I've not tested it so it might not work, but it should hopefully get you off and running in the right direction. HTH.

-geoff

[deleted]

Tiny note:
These kind of queries will never be able to use indexes, because they process data on the fly. Consequently, this query will be quite slow.

If you need to do this type of query many times, or on lots of records, I suggest you add a new column to the table, and use that to store the formatted version of the name (with 'THE' at the end). Then you will be able to use indexes, and things will speed up dramatically.

Anon

That worked great. Thank you!!!

Weird--I didn't realize MYSQL supported IF statements. I can't find a single reference to them in the MYSQL manual.

Anyway, due to the large number of items in my database, it made more sense to use Vincent's approach.

However, since storing the name twice is kinda kludgy, I just created an ENUM field that indicates whether or not the name begins with 'The '--and I also removed 'The ' from all of the movie names.

This way, sorting is speedy, the database is more compact, and I can easily append 'The' as a prefix or suffix whenever it's needed.

Thanks for the help, guys!

[deleted]

MySQL's IF is an alternative to the SQL-standard CASE:

SELECT CASE field WHEN value THEN othervalue ELSE somevalue END;

Beware of ENUM types, the are not as good as they seem. If you want to just store a boolean to indicate 'this name contains THE', then you may be better off using a tinyint (or INT if you want to be compatible)

brainstorm mode:
You could even decide to store the prefixes in a different field, and concat them on select:

SELECT CONCAT(prefix, name) FROM table ORDER BY name;

something like that, then you can do the same ordering for names that begin with other words too, like 'A midsummernights dream' and 'J. F. Kennedy'