1

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
Improve this question
5
  • 3
    In your result I don't see z27. How do you want to handle different letters in your sorting? Commented Apr 25, 2011 at 20:14
  • 3
    Is a17 > b16 ? Is the number or letter more important? Commented Apr 25, 2011 at 20:16
  • 2
    Is the structure consistent? I.e., always one and only one letter followed by nothing but digits? Commented Apr 25, 2011 at 20:22
  • 1
    What about k25 and k26? Should those be first? Commented Apr 25, 2011 at 20:25
  • 'k' and 'z' are letters for several types of competition. Since there is no chance to sort it in numeric order, I'd separate that latter in another column. Thank you for answers! Commented Apr 25, 2011 at 20:29

5 Answers 5

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

Comments

1

Eventlyally get this :)

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

Comments

0

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
Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.