[RESOLVED] Sorting Trouble

jameseymour

I have a table that has a title field that is a char type. Some book titles will start with numbers and i want them to be ordered properly. Right now titles without numeric characters sort perfectly. But I get this output from numeric titles:

15 minutes to get it right
20 top quilting tips
25 minute book of something
5 minute sports consult
5-minute consult
5-minute emergency consult
5th book of something
7 quick steps
8th book something

Is there an easy way to sort these properly using sql? If not what would be the best way to approach it with php?

Piranha

They are ordered properly. Sorting is taking the first character and order by that. Second, if the first character is same as the first then sort by second character. And so on.

If you want to order it in another way I suggest that you show how you want it ordered.

jameseymour

I want to sort them numerically first, but they are strings. So the output would be something like:

5 minute sports consult
5-minute consult
5-minute emergency consult
5th book of something
7 quick steps
8th book something
15 minutes to get it right
20 top quilting tips
25 minute book of something

Am I going to have to cut the srting up and if there is numeric characters sort on that first or something?

Piranha

Unfortunatley I can't help you with that, I simply haven't the skill required. I think it is possible, hopefully some SQL guru can help you out.

jameseymour

Ah, thanks anyway comrade.

Roger_Ramjet

Yes you are going to have to grab the number off the front of the string if you want a numeric sort order, or add a leading zero to 1-9 for string sort

05 minute sports consult
05-minute consult
05-minute emergency consult
05th book of something
07 quick steps
08th book something
15 minutes to get it right
20 top quilting tips
25 minute book of something

jameseymour

I was afraid of that. Guess I will have to use php to sort titles with leading numeric characters. Thanks for the info.