From: http://blog.chinaunix.net/uid-7692530-id-2567549.html
1. sort by date only, ignore year
> Select date, description
From table_name order
Month (date), dayofmonth (date );
Note: Using dayofyear alone will cause errors, such as the same level as the 3-1 day of the non-leap year.
|
2. Sorting point fractional IP Address
> Select IP from table_name order
Inet_aton (IP );
Or int unsigned is used to represent the IP address when designing the table.
|
3. Sort the special values in a column to the top
For example, if you want to display the names of Lulu in the table at the top of the list
> Select name from table_name order by if (name = 'lulu',), name
;
You can also replace the if condition with the corresponding statement as needed.
|
4. Sort the content of a column in the User-Defined order.
For example, if you want to sort the table names by Lulu, XiXi, Baba, and mama in an unconventional order:
> Select name from table_name order
Field (name, 'lulu', 'xixi', 'baba ', 'Mama ');
|
5. Sort enumeration types
By default, the enumeration type is Numeric sorting. If you want to sort the data in string format.
> Select name from table_name order by cast (name
As char );
If you want to change the default order of Enum sorting, you can alter table
Alter table table_name modify name
Enum ('lulu', 'xixi', 'Mama', 'baba ');
|
6. sort by a string of the CSV type
For example, if the content of a column m_str is in the form of a abc-321-mno-jkl, you want to sort the second column
> Select m_str from table_name
Order by substring_index (m_str, '-', 2), '-',-1 );
|
