CODEWITHSUNDEEP
phpmysqlsqlsortingsql-order-by
Maxime
Maxime

Reputation: 2234

SQL Order by ... ASC

I have a problem with a SQL query. I wish to have my results sorted in the ascending order but the problem is that the DB sort me the results only according to the first figure, whereas logically, 2 should be before 19 for example.

Here's my request and a screenshot of the result which does not go at all :

SELECT * FROM mail WHERE titre LIKE '%$mot%' ORDER BY mail.titre ASC

Very bad sort...

What I want is all the results sorted like this : 1 2 3 4 5 ... 101 102 103 ... 8845 8850 ... and so on.

Would you have an idea to this problem?

Regards.

Upvotes: 0

Views: 212

Answers (3)

Beri
Beri

Reputation: 11610

Won't do. You have string/char[] column, and order you are looking for works on numbers only. You need to convert this column to numbers, if it's possible.

You can use cast:

CAST(mail.titre,int)

Examples are here: Converting text column to integer in MySQL

Upvotes: 3

Robert
Robert

Reputation: 1343

As far as I can see the field is a varchar/text... So to sort them correctly you would have to cast the field (if possible)

Example:

SELECT * FROM mail WHERE titre LIKE '%$mot%' ORDER BY CAST(mail.titre AS int) ASC

Upvotes: 1

Jens
Jens

Reputation: 69440

I think you column is decalred as char. So the DBMS is sorting by string value. You have to convert your values to int in the sort clause.

SELECT * FROM mail WHERE titre LIKE '%$mot%' ORDER BY convert(mail.titre, unsigned integer) ASC

Upvotes: 1

Related Questions