So I have a table with a column in this form

someColumn |
---|

1 |

A |

2 |

C |

3 |

10 |

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.

## Leave a Reply