This should be simple, but I haven't used a subquery in quite this way, and
have little experience with GROUP BY.
I have 2 tables
prod: prod_id /* a unique key */
prod_rel: prod_id /* values that tie prod_rel to prod */
A prod_id in prod might have 0 or more matching rows in prod_rel. I want to
find the prod_id of the entry in prod that has the most dependent rows in
prod_rel, and how many dependent rows it has.
SELECT MAX(c) FROM
(SELECT COUNT(prod_rel.prod_id) AS c
FROM prod, prod_rel
WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id)
AS t1;
gives me
+--------+
| MAX(c) |
+--------+
| 7 |
+--------+
That's all fine and dandy, but I haven't figured out how to get the actual
value of prod_id that goes with that maximum value. I tried
mysql> SELECT MAX(c), id FROM
-> (SELECT COUNT(prod_rel.prod_id) as c, prod_rel.prod_id as id
-> FROM prod, prod_rel
-> WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id)
-> AS t;
and got
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with
no
GROUP columns is illegal if there is no GROUP BY clause
I had a feeling that I couldn't just use id in the outer select, but how do
I get it? I tried adding GROUP BY t.prod_id, but that just gave me the same
error.
Please help me find my way.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
