MySQL sort options

[aksival]

Say we have data:

A
B
C
D
E
F
G
H
I
J

1
2
3
4
5
6
7
8
9

By default, MySQL is sorting them A-Z first, then 0-9. Is there a simple way to sort them 0-9 first, then A-Z? So the results would appear:

1
2
3
4
5
6
7
8
9
A
B
C
D
E
F
G
H
I
J

Make sense (hopefully)?

Thanks,
aksival

[jheitz]

Hmmm... strange. Given that the ASCII code of 0 is lower than that of A I would have expected MySQL to show the 0 first.

Maybe Sort by ASCII code... There might be a function in MySQL such as ASCII() or ORD() you could employ to do something like:

SORT ORD(ColumnName)??

[jheitz]

Sorry, if such function exists, you will need to get the ORD() or ASCII() value of the first character in the column, so you'll actually need to do something like:

SELECT ...
FROM ...
WHERE ...
ORDER BY ORD(SUBSTRING(1,1,ColumnName))

NOTE: The above syntax is certain to be incorrect...

[nnichols]

I saw the following method suggested somewhere -

order by
    case when isNumeric(alphanumericcolumn)=1
            then cast(alphanumericcolumn as integer)
            else 9999999999 end
     , alphanumericcolumn

but I don't think I would want to use it on very large datasets.

[nnichols]

What character set and collation are you using for this column? The default charset and collation (latin1_swedish_ci) gives the sort order your after.

[aksival]

Hmm...I'll have to check on that when I get back in the office tomorrow. I believe the results are being output this way because of this:

ORDER BY (fieldname + 0)

Which, for some reason, allowed numbers like the following to be sorted like this:

00
000
1
2
3

This might have something to do with the sort order being somewhat abnormal. I'll post again in the morning.