Mysql - What happen in my Order by?

Question

I have table like

CREATE TABLE IF NOT EXISTS example 
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
primary key ( id ))

I insert to table 20 record from 1 -> 20 like

id | name
1  | example 0
2  | example 1
...
20 | example 19

I do query like

SELECT *
FROM `example`
ORDER BY `name` DESC

But results are

id | name
10 | example 9
9  | example 8
...
1  | example 0

I think that is

id | name
20 | example 19
19 | example 18
...
1  | example 0

How can I do what i think. Thanks

And you are 100% sure there are 20 records in the table? Like, really 100% sure? — N.B., Jul 26 '13 at 13:45
@N.B. sorry I typing error I just edit my record plz see it again — LookAtMeNow, Jul 26 '13 at 13:55
@LookAtMeNow is the entire value of `name` = `20 example 19`? What exactly is stored in the name column? — Iron3eagle, Jul 26 '13 at 14:03

score 2 · Answer 1 · answered Jul 26 '13 at 13:45

2

It is a string sort, not a numeric one

ASCII wise, descending, '9' is before '20' as expected. THis can also be thought of as '9 ' (trailing space) being compared to '20'

String sorting is character-wise.

answered Jul 26 '13 at 13:45

gbn

422,506
82
585
676

@defaultNINJA: It's tagged MySQL – gbn Jul 26 '13 at 13:50

Iron3eagle · Accepted Answer · 2013-07-26T14:16:10.127

1

Use SUBSTRING to grab the number after the space with LOCATE and then do ORDER BY should work while using CAST.

SELECT   *
FROM     example
ORDER BY CAST(SUBSTRING(name,LOCATE(' ',name)+1) AS SIGNED) DESC

edited Jul 26 '13 at 14:16

answered Jul 26 '13 at 13:58

Iron3eagle

1,077
7
23

but ORDER BY (name+0) ASC and ORDER BY (name+0) DESC has same results :(? – LookAtMeNow Jul 26 '13 at 14:03
@defaultNINJA I just edit them plz see again – LookAtMeNow Jul 26 '13 at 14:07
@LookAtMeNow see updated answer. may need to adjust `SUBSTRING` – Iron3eagle Jul 26 '13 at 14:10

Praveen Prasannan · Answer 3 · 2013-07-26T13:51:43.567

0

SELECT *
FROM `example`
ORDER BY CAST(name AS UNSIGNED)desc;

fiddle

edited Jul 26 '13 at 13:51

answered Jul 26 '13 at 13:45

Praveen Prasannan

7,093
10
50
70

The ID is just an auto-incremented field, which should not be used for sorting. In this specific case, OP is asking to sort by name. Sorting by a different field that only accedentally has the right order, does not solve the underlying problem. – GolezTrol Jul 26 '13 at 13:47
@GolezTrol check now. Updated – Praveen Prasannan Jul 26 '13 at 13:52
@Praveen please see my edit results:( – LookAtMeNow Jul 26 '13 at 14:14

score 0 · Answer 4 · answered Jul 26 '13 at 13:49

It's doing alpha ordering because it's a string.

Example 2 is after Example 1000.

Some options You could split it in to two fields names and number You could do a lot of messing about with String functions to split it up for a sort You could add leading zeros Example 0002 is before Example 1000.

score -1 · Answer 5 · answered Jul 26 '13 at 13:47

-1

You are ordering by the name which will result in an alphebetic order. Try

    ORDER BY id desc;

answered Jul 26 '13 at 13:47

June Lewis

355
1
6
28

The ID is just an auto-incremented field, which should not be used for sorting. In this specific case, OP is asking to sort by name. Sorting by a different field that only accedentally has the right order, does not solve the underlying problem. – GolezTrol Jul 26 '13 at 13:49

Mysql - What happen in my Order by?

5 Answers5