List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:October 31 2006 8:53pm
Subject:Max of Count
View as plain text  
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



Thread
Max of CountJerry Schwartz31 Oct
  • Re: Max of CountDan Buettner31 Oct
    • RE: Max of CountJerry Schwartz1 Nov
      • Re: RE: Max of CountDan Buettner1 Nov