Thanks, Dan, that does indeed work; but as you said it only gives one record. Removing the limit gives them all to me, which for visual inspection works fine.
This started out as a "Gee, I wonder..." task, so as a learning experience I'd like to figure out how to get all of (and only) the records whose count is the maximum. It seems tantalizingly out of reach. I know what MySQL is telling me (I can use MAX without a GROUP only if I don't use other columns that aren't functions), but not how to fix it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Dan Buettner [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 31, 2006 5:49 PM > To: Jerry Schwartz > Cc: mysql@lists.mysql.com > Subject: Re: Max of Count > > 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]
