List:General Discussion« Previous MessageNext Message »
From:Tobias Asplund Date:October 16 2003 8:13pm
Subject:Re: natural sorting
View as plain text  
If you have 4.0.2 or later you can use the CAST() function, if you have
earlier you should be able to emulate it with the BINARY keyword for the
ORDER BY clause, examples below:

flupps@localhost:tmp > CREATE table sort (
    ->   num int
    -> );
Query OK, 0 rows affected (0.00 sec)

flupps@localhost:tmp > INSERT sort (num) VALUES (1), (2), (3), (10), (11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

flupps@localhost:tmp > SELECT num FROM sort ORDER BY num;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+
5 rows in set (0.00 sec)

flupps@localhost:tmp > SELECT num FROM sort ORDER BY BINARY num;
+------+
| num  |
+------+
|    1 |
|   10 |
|   11 |
|    2 |
|    3 |
+------+
5 rows in set (0.00 sec)

flupps@localhost:tmp > SELECT num FROM sort ORDER BY CAST(num AS CHAR);
+------+
| num  |
+------+
|    1 |
|   10 |
|   11 |
|    2 |
|    3 |
+------+
5 rows in set (0.00 sec)


On Thu, 16 Oct 2003, Michael Winston wrote:

> Is there any way to do "natural sorting" in MySQL?  That is:
> Chapter 1
> Chapter 2
> Chapter 10
>
> instead of
>
> Chapter 1
> Chapter 10
> Chapter 2
>
> ?
>
> If not, are there any plans for this feature in future releases?
> Would be pretty helpful...
>
> Michael
>
>
Thread
natural sortingMichael Winston16 Oct
  • Re: natural sortingDan Nelson16 Oct
    • Re: natural sortingMichael Winston17 Oct
      • Re: natural sortingRoger Baklund17 Oct
        • Help in queryingdelz17 Oct
          • RE: Help in queryingRob17 Oct
  • Re: natural sortingTobias Asplund16 Oct