So I have a table with a column in this form
When you ORDER BY someColumn you get result in this order 1,10,2,3,A,C
as you can see the number sequence is all messed up, 10 doesn’t go after 1
to fix that you can do ORDER BY (someColum + 0) to convert the sequence to integers
but that leaves the characters out of order now. The characters get sorted by creation date
this is a very inconvenient problem.
To keep this short this is the solution
SELECT someColum as sort, someColumn, otherColums... WHERE someOther = 'somevalue' ORDER BY sort ASC, (someColumn +0)ASC
by Adding a sort column using ‘someColum’ we can now perform two sorting operations on the same Column first by chars then by converting ‘someColum’ to integerss. This will return an output like this 1,2,3,10,A,C. If you swap the ‘ ORDER BY sort ASC, (someColumn +0) ASC’ to’ ORDER BY (someColumn +0) ASC ,sort ASC’, now you get the Characters first A,C,1,2,3,10. You can also change the order to Descending by using DESC.