MySQL: How to sort numbers with some data also containing a letter

New coder here. Here is an example of the data I am trying to sort:

2
3a
3b
4
10

When I include this in my query:

ORDER BY mini_number +0

I get this:

2
3b
3a
4
10

Could someone please offer a way I can sort this in the way I am intending.

ORDER BY CAST(mini_number AS UNSIGNED), mini_number

Thank you greatly. I am really not familiar with the CAST function at all, but it does do what I need it to do. I’ll look into what else I can do with CAST now. Thank you.

don’t forget the second item in the ORDER BY, it’s needed too

For clarity, this is so that the order is such that 10a comes before 10b etc (because the first order by term makes them both 10)…

I haven’t really kept up but my last recollection is that without the second term, the behavior of the sort is undefined for equal values? Or has it been defaulted to an index-based order?

no idea…

i don’t like relying on default behaviour when syntax is available to get what you want

but wait, there’s more!!

i don’t like utilizing syntax (e.g. FORCE INDEX) when i know the optimizer’s default is a lot smarter than me

1 Like