ORDER BY tricky problem

[NickyM999]

Hi there,

I have a MySQL table of books. The client wants me to order the books aphabetically by title. No problem, very easy. However, if the book has a title that begins with A, THE, or AN, then I should ignore this and sort on the second word. IE book title 'A tale of two cities' would be listed with all the other books beginning with 'T' and 'The colour of money' would reside with books beginning with C.

Anyone got any sensible ideas?

TIA

Nick

[RadioGeek]

Here is what I would do...and it's just an idea.

I would create a mirror table of the one you have with ONE addition: TITLE_PREFIX.

I would then sort of "copy" the original table to the new table. While it's copying, have it bounce and "A", "AN", "THE", etc words into the TITLE_PREFIX and leave the title itself alone. So that when you order, you are dealing ONLY with the title. When you want to display the whole title, it could be as simple as:

$myrow["TITLE_PREFIX"] $myrow["TITLE"]

Then I would just copy it back so that you don't have to change any of your scripts too drastically.

Just an idea...it may not be the best way, but it could work.

[ultraslacker]

You can do this in the select with string functions. Check out the manual to find out more about functions.

SELECT CASE WHEN SUBSTRING(title, 1, LOCATE(' ', title)) IN ('A ', 'The ', 'An ')
THEN SUBSTRING(title, LOCATE(' ', test))
ELSE title END AS sort_title, title
FROM book
ORDER BY sort_title;