The column alias could be used in the GROUP BY, HAVING or ORDER BY clause, but not in the WHERE clause.
It's possible to perform a sort using an expression result:
SELECT name, MONTH(birthday) m FROM t ORDER BY MONTH(birthday);
SELECT name, MONTH(birthday) m FROM t ORDER BY m;
A query might run faster if the ORDER BY uses an indexed column.
ORDER BY can be used with DELETE or UPDATE to force rows to be deleted or updated in certain order.
Non-binary strings sort in the order defined by their collation. If the collation is case-insensitive, the sort result is also case-insensitive.
If the collation is binary, the numeric value determines the sort order.
The sort order for members of an ENUM or SET column is based on their internal numeric values, which means, for ENUM, it follows the defined order in the column definition, for SET, it follows the sum values of internal numeric values.
You can change these behaviours by using CAST():
SELECT mon FROM t ORDER BY CAST(mon AS CHAR);
Limiting a Selection Using LIMIT:
... LIMIT row_count
... LIMIT skip_count, row_count
When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT.
DISTINCT result may be different by the column collations.
A difference between DISTINCT and GROUP BY is that DISTINCT doesn't cause row sorting (in MySQL, GROUP BY does cause sorting).
DISTINCT can be used with the COUNT() funtion to count how many distinct values a column contains. In this case, NULL values are ignored.
SELECT COUNT(DISTINCT col1) FROM t;
It's possible to perform a sort using an expression result:
SELECT name, MONTH(birthday) m FROM t ORDER BY MONTH(birthday);
SELECT name, MONTH(birthday) m FROM t ORDER BY m;
A query might run faster if the ORDER BY uses an indexed column.
ORDER BY can be used with DELETE or UPDATE to force rows to be deleted or updated in certain order.
Non-binary strings sort in the order defined by their collation. If the collation is case-insensitive, the sort result is also case-insensitive.
If the collation is binary, the numeric value determines the sort order.
The sort order for members of an ENUM or SET column is based on their internal numeric values, which means, for ENUM, it follows the defined order in the column definition, for SET, it follows the sum values of internal numeric values.
You can change these behaviours by using CAST():
SELECT mon FROM t ORDER BY CAST(mon AS CHAR);
Limiting a Selection Using LIMIT:
... LIMIT row_count
... LIMIT skip_count, row_count
When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT.
DISTINCT result may be different by the column collations.
A difference between DISTINCT and GROUP BY is that DISTINCT doesn't cause row sorting (in MySQL, GROUP BY does cause sorting).
DISTINCT can be used with the COUNT() funtion to count how many distinct values a column contains. In this case, NULL values are ignored.
SELECT COUNT(DISTINCT col1) FROM t;
09/28: SQL:1999
Sidebar
