MySQL: Sorting by title problem

Hello, I am pretty new to mysql and need some help with sorting.

I have a few titles similar to this:

title part 1
title part 2
title part 3 & 4
title part 5
title part 6
title part 7 & 8

These title are added in mysql in no particular order so I cannot order them by date or id or just by the title.

I have tried using this:

select * from titles order by length(title), title ASC

However it doesn't work as the title with 2 parts messes it.

Does anyone know how I can order those in a ASC order?

Thank you.

 

i would create a new column on that database, call it order make it as an int

they given each row a value , 1,2,3,4,5 etc

and order by that, you can then create and change the order to however you want it

 

You want to sort titles alphabetically? In that case, why don't you use 'ORDER BY title' ?

 

ORDER BY TITLE DESC

ORDER BY TITLE ASC

 

if there is "title part 10" then it would be overlapped to "title part 2". this could be happen too.

I agree with marksailes about adding new int field / column so you can fill it with any number you want and use it to sort. If you ever use wordpress, when you create a page (not post) there will be an "order" field. After published, the order of pages will be sorted by that field.

 

Hi dramalover,

MySQL doesn't include a natural sort option but this SQL should work for you

select * from titles ORDER BY substring_index(title, ' ', 1), substring_index(title, ' ', -1)+0;

HTH