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
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
Eventlyally get this :)
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
You can't sort it that way, ether add a 0 (k09 - k04) or add another field that is an int.
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