Ordering with numbers and text

I have another ORDER BY question. This is a MySQL database accessed via PHP.
There is a column (class) I want to use for ordering which is varchar type, the values in it are mainly integers, except a few are text strings.
So that the numbers order properly (as if int type) I am currently converting to decimal.
Simplified example:-

"SELECT stuff FROM table WHERE this = :that ORDER BY something, convert(`class`, decimal)"

That works except that the text gets converted to a value of 0 so comes before the numbers.
What I want is for the numbers to sort as integers then the text always comes after the numbers, not before.

ORDER BY CASE WHEN 0+class = 0 THEN 'humpty' ELSE 'dumpty' END , 0+class , class

1 Like

That appears to be working. Thank you.
I thought it must be something involving CASE but was not sure how to combine that with treating the numbers as integers, not text.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.