If you have a table with a varchar or text column, which may or may not contain text-based integer values, it’s sometimes hard to do natural sorting on MySQL or Postgres. By that what I mean is, if you have
col1
——
a1
a2
a10
a20
and if you apply “order by col1″, the sorting result would be something like
col1
——
a1
a10
a2
a20
The easiest way to go around this is to first order by the length of the value and then the actual value. So in MySQL, you will have
… order by LENGTH(col1) ASC, col1 ASC
and in Postgres
.. order by CHAR_LENGTH(col1) ASC, col1 ASC
WA~LA~ saved me time.
