I dont think it is possible although I have never searched hard for the ability. It seems unlikely - since you get to name the columns in the table and the columns in the query (using 'as'). I suggest you use one of those abilities. -----Original Message----- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 11:10 PM To: Dave Pullin Cc: mysql@lists.mysql.com Subject: Re: Aggregate functions in ORDER BY
Many thanks for that Dave. Do you know whether it's possible for MySQL to return fully qualified column names by default? For example, I'd like select * from foo; to return +--------+----------+ | foo.a | foo.b | +--------+----------+ | 1 | Rex | | 2 | Buddy | | 3 | Sam | | 4 | Lucky | | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger | | 9 | Speedy | +--------+----------+ instead of +--------+----------+ | a | b | +--------+----------+ | 1 | Rex | | 2 | Buddy | | 3 | Sam | | 4 | Lucky | | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger | | 9 | Speedy | +--------+----------+ Thanks, Yasir All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -----Original Message----- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR 1111 (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1);