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.

Permalink

Permalink

how if i want zero included?? like X,Y,Z,0,1,2,3,4….

Permalink

I haven’t tried but it should work just fine

Permalink

I haven’t tested but I don’t think 0 would cause a problem, my example was just a generalization of possible values

Permalink

Very very great help.

Thank you