MySQL. Numeric sort by string ID

I'm creating live score for dragracing competition. I've got table in database

id      time
k2      16,010
k4      15,11
k25     15,819
k26     15,114
z27     19,696

I need to get that table in reverse order by id. For example, if select SELECT id FROM online order by id desc the result would be sorted as a string.

id
k9
开发者_JAVA技巧k8
k7
k6
k5
k4
k3
k17
k16
k15
k14

I need 17-14-9-4

Upd. THANKS FOR ALL!!! This one helped

SELECT CAST( replace( id, 'k', '' ) AS SIGNED ) AS sort
FROM online
ORDER BY `sort` DESC

Split the field into two, one with the "k" and another with the number.

Alternatively, less preferable, if you use PHP, you can use the nat_sort() method or, probably, usort() using the strnatcmp() function for comparing this particular field.

The problem is your schema. You should have a column for the letter part. Then you can do

SELECT CONCAT(letter, id) FROM online ORDER BY id ASC

Except you should name letter something descriptive.

EDIT: If the letter is important in the sorting (it's not clear whether it is or not) then you would do:

SELECT CONCAT(letter, id) FROM online ORDER BY letter, id ASC

Assuming the structure is consistent with a single letter followed by digits, you could do something like:

Select ...
From online
Order By Substring(Id, 1, 1) Desc
    , Cast( Substring(Id, 2, Len(id)) As int ) Desc

Eventlyally get this :)

SELECT CAST( replace( id, 'k', '' ) AS SIGNED ) AS sort
FROM online
ORDER BY `sort` DESC

You can't sort it that way, ether add a 0 (k09 - k04) or add another field that is an int.