I have strings such as M1 M3 M4 M14 M30 M40 etc (really any int 2-3 digits after a letter)
When I do " ORDER BY name " this returns:
M1, M14, M3, M30, M4, M40
When I want:
M1, M3, M4, M14, M30, M40
Its treating the whole thing as a string but I want to treat it as string + int
Any ideas?
I couldn't get this working for my issue which was sorting MLS Numbers like below:
V12345 V1000000 V92832
The problem was V1000000 wasn't being valued higher than the rest even though it's bigger.
Using this solved my problem:
ORDER BY CAST(SUBSTR(col_name FROM 2) AS UNSIGNED) DESC
Just removed the SUBSTR(col_name FROM 1 FOR 1)
You can use:
order by name,SUBSTRING(name,1,LENGTH(name)-1)