CODEWITHSUNDEEP
phpmysqldatabase
user2052643
user2052643

Reputation: 13

order by seriq asc show wrong results

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

?

Upvotes: 0

Views: 394

Answers (4)

echo_Me
echo_Me

Reputation: 37233

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);

Upvotes: 3

Sasha Pachev
Sasha Pachev

Reputation: 5326

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.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108380

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.

Upvotes: 0

Mike Christensen
Mike Christensen

Reputation: 91598

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

Upvotes: 0

Related Questions