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

Databases
1

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.

2 
ORDER BY CAST(mini_number AS UNSIGNED), mini_number
3

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.

4

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

5

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?

6

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
7

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