Sorting questions (MySQL)

I have a couple of ORDER BY questions that I hope someone can and want to answer.

  1. Is there any easy way I can achieve a natural sorting order with both numbers and letter (i.e. 1, 2, 10, 11, 20, 100, a2, a100, b5 etc.) in MySQL? I’ve found some suggestions involving ASC 0, but they don’t seem to work when letters are involved.

  2. Is there any way to create a sort on SQL level (rather than, e.g., PHP) that puts all rows with a certain value at the end of the list, for example 0 in an INT column that contains years. In the cases where the years is unknown the rows have 0, which I would like to come last in both ascending and descending order. Similarly with empty strings in other column types.

Thanks.

  1. nothing easy… i recommend a second column, declared INTEGER, for sorting

  2. use a CASE statement

for example, to put 0’s last in ascending order…

ORDER
    BY CASE WHEN foo = 0
            THEN 'humpty'
            ELSE 'dumpty' ASC
     , foo ASC

Great suggestion, as always. Thanks.

Probably great as well. I’ll just have to understand it before deciding. :slight_smile:

Edit: I obviously need to read up a little more on CASE syntax, but is the first ASC a typo for END, or have I done something wrong that makes it work with END but not ASC?

aaargh, my bad, yes, i forgot to put END before ASC there

ASC, of course, because you want dumpty rows before humpty rows

i know ASC is default, but in these cases it helps to have it there explicitly

Ah, yes, thanks for the clarification.