| Bug #48688 | sort by newly-created field name not working | ||
|---|---|---|---|
| Submitted: | 11 Nov 2009 13:21 | Modified: | 11 Dec 2009 14:09 |
| Reporter: | Victor Chung | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 5.1 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | sort order field name | ||
[11 Nov 2009 14:09]
Valeriy Kravchuk
Please, try this query: select name, birth, month(birth) as `birthday in month` from pet where month(birth)<06 order by `birthday in month`; You can use backticks or double quotes for column alias, NOT single quotes. Single quotes are for sting literals, so you order by some constant string in your problematic query.
[12 Dec 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Pals, look at the results of the following two commands, they are supposed to be the same, but they are not: mysql> select name, birth, month(birth) as 'birthday in month' from pet -> where month(birth)<06 -> order by 'birthday in month'; +---------+------------+-------------------+ | name | birth | birthday in month | +---------+------------+-------------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Nothing | 2001-01-01 | 1 | +---------+------------+-------------------+ 13 rows in set (0.02 sec) mysql> select name, birth, month(birth) as 'birthday in month' from pet where month(birth)<06 order by month(birth); +---------+------------+-------------------+ | name | birth | birthday in month | +---------+------------+-------------------+ | Nothing | 2001-01-01 | 1 | | Fluffy | 1993-02-04 | 2 | | Fluffy | 1993-02-04 | 2 | | Fluffy | 1993-02-04 | 2 | | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Claws | 1994-03-17 | 3 | | Claws | 1994-03-17 | 3 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Buffy | 1989-05-13 | 5 | | Buffy | 1989-05-13 | 5 | | Buffy | 1989-05-13 | 5 | +---------+------------+-------------------+ 13 rows in set (0.00 sec) How to repeat: mysql> select name, birth, month(birth) as 'birthday in month' from pet -> where month(birth)<06 -> order by 'birthday in month'; mysql> select name, birth, month(birth) as 'birthday in month' from pet where month(birth)<06 order by month(birth);