2
$\begingroup$

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

Improve this question
$\endgroup$
4
  • 2
    $\begingroup$ And you are 100% sure there are 20 records in the table? Like, really 100% sure? $\endgroup$ Commented Jul 26, 2013 at 13:45
  • 1
    $\begingroup$ It might have something to do with "natural sorting"? $\endgroup$ Commented Jul 26, 2013 at 13:45
  • $\begingroup$ @N.B. sorry I typing error I just edit my record plz see it again $\endgroup$ Commented Jul 26, 2013 at 13:55
  • 1
    $\begingroup$ @LookAtMeNow is the entire value of name = 20 example 19? What exactly is stored in the name column? $\endgroup$ Commented Jul 26, 2013 at 14:03

5 Answers 5

Reset to default
2
$\begingroup$

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.

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

1 Comment

@defaultNINJA: It's tagged MySQL
1
$\begingroup$

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
Improve this answer
$\endgroup$

3 Comments

but ORDER BY (name+0) ASC and ORDER BY (name+0) DESC has same results :(?
@defaultNINJA I just edit them plz see again
@LookAtMeNow see updated answer. may need to adjust SUBSTRING
0
$\begingroup$

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.

Improve this answer
$\endgroup$

Comments

0
$\begingroup$ 
SELECT *
FROM `example`
ORDER BY CAST(name AS UNSIGNED)desc;

fiddle

Improve this answer
$\endgroup$

3 Comments

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 check now. Updated
@Praveen please see my edit results:(
-1
$\begingroup$

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

    ORDER BY id desc;
Improve this answer
$\endgroup$

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.