[RESOLVED] mysql query order by prob

[perpetualshaun]

I'm pulling catalog page numbers from a table, and trying to get them in the correct order. I have used the CAST() on my query before, but I don't think that will work in this case since these catalog page numbers start w/ a letter. So for example when I pull the As right now, it orders them as A-1, A-10, A-11, A-2, etc. How can I get them in the correct order?

[nemonoman]

You'll need to sort by dividing the element into substrings:

SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos)
if pagenumber is the column

SUBSTRING(pagenumber,1,1), #returns A, B, C, etc
SUBSTRING(pagenumber,3) #1,2,3,4

You'll probably need to cast the 1,2,3,4 etc as nums not strings: I do it this way; works the same as CAST: SUBSTRING(pagenumber,3) * 1

Final Clause is:

ORDER BY
SUBSTRING(pagenumber,1,1), SUBSTRING(pagenumber,3) * 1