order by seriq asc show wrong results

Question

Why when i use order by seriq asc and have numbers like "10000" "100000" "97000"

script show me the results:

1: 10000
2: 100000
3: 97000

?

The result is an alphabetic sort. – Oerd Feb 07 '13 at 23:51 — Oerd, Feb 07 '13 at 23:51

echo_Me · Accepted Answer · 2013-02-08T00:01:51.830

3

because they are stored in your column as strings

1- try to change the column seriq from VARCHAR/CHAR to INT.

2- You can use CAST() to convert from string to int. e.g. SELECT CAST('123' AS SIGNED);

edited Feb 08 '13 at 00:01

answered Feb 07 '13 at 23:34

echo_Me

37,078
5
58
78

1

What's 2? I hate 1 item lists. – Kermit Feb 07 '13 at 23:41
it comes the 2 , but the best solution is to make column as int – echo_Me Feb 07 '13 at 23:43
it mysql, it should be `CAST('123' AS SIGNED)` or *UNSIGNED*. – John Woo Feb 07 '13 at 23:57

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

Your seriq column must be a numeric column for values to be sorted numerically. If it's a text column, values will be sorted in alphabetical order as per their collation. For example:

CREATE TABLE Test
(
   Foo int  
);

INSERT INTO Test VALUES (10000);
INSERT INTO Test VALUES (100000);
INSERT INTO Test VALUES (97000);

select * from Test order by Foo asc;

Fiddle

edited May 23 '17 at 11:56

Community

1
1

answered Feb 07 '13 at 23:38

Mike Christensen

88,082
50
208
326

I honestly think it would be easier to put it in a temp table. – Kermit Feb 07 '13 at 23:43

score 0 · Answer 3 · answered Feb 07 '13 at 23:39

The quick fix is to have MySQL convert the string to a numeric value in the ORDER BY clause.

Change your query to:

ORDER BY seriq+0 ASC

Note that MySQL will attempt to convert any string to a numeric value, reading characters up to the first "invalid" character, e.g. '123A' will be converted to a numeric value 123.

score 0 · Answer 4 · answered Feb 07 '13 at 23:41

Changing the column type to int is the best solution as it will give you the best data storage and performance. However, if that is not an option, the following workaround is possible:

select * from foo order by seqid+0

This forces a type cast of the order by column to int, and the sort happens numerically.

order by seriq asc show wrong results

4 Answers4