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);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
