This might work for you, Jerry - you're on the right track, pretty close.
SELECT prod.prod_id, COUNT(*)
FROM prod, prod_rel
WHERE prod.prod_id = prod_rel.prod_id
GROUP BY prod.prod_id
ORDER BY 2 DESC
LIMIT 1
Be aware that using the LIMIT 1 may be misleading in that if you have
multiple entries with the same highest number of relations, you'll
only see one. That may or may not matter to you.
HTH,
Dan
On 10/31/06, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
