Sorting VARCHAR data in mysql

Từ: http://www.linuxweblog.com/blogs/sandip/20060126/sorting-varchar-data-mysql

Here’s a quick tip at sorting VARCHAR type data in mysql database with values in a column.
With the default sort, it would look something like below:

mysql> SELECT column FROM table_name ORDER BY column; 
column
======
100
1000
10000
200
2000
20000
...

Now with “… ORDER BY column+0”, I get it sorted right:

mysql> SELECT column FROM table_name ORDER BY column+0; 
column
======
100
200
1000
2000
10000
20000
...

This is a quick fix instead of sorting to CAST operator.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s