Maybe I'm misunderstanding you, if so use the HAVING and pretend I never said anything, other wise:
A HAVING clause is essentially a WHERE clause, it just does the checks after it groups the rows. So you can't select which row in a group to return, as it's already been grouped. You may be getting good results with having because the rows you want are the first in the group and are being chosen, but you'll find that if they aren't first that group won't return at all. Chris -----Original Message----- From: Peter Sap [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:57 PM To: [EMAIL PROTECTED] Subject: Re: How to 'customize' GROUP BY? Hi Chris, Please tell me why a group by with a having shouldn't work in this case. I tested it with a small table and it works fine, at least in this case. Peter Sap. ----- Oorspronkelijk bericht ----- > Van: "Chris" <[EMAIL PROTECTED]> > Aan: "List: MySQL" <[EMAIL PROTECTED]> > Verzonden: vrijdag 21 november 2003 2:19 > Onderwerp: RE: How to 'customize' GROUP BY? > > > > That won't do what it looks like he wants. > > > > GROUP BY can't do that :( I wish it could. > > > > This issue recently came up for me, and I initially was using Temporary > > table, but then had to switch to a 'pregrouped' table so I could retrieve > > data properly. > > > > -----Original Message----- > > From: Peter Sap [mailto:[EMAIL PROTECTED] > > Sent: Thursday, November 20, 2003 4:25 PM > > To: List: MySQL > > Subject: Re: How to 'customize' GROUP BY? > > > > > > Hi Yves, > > > > You can use a having clause to work with groups: > > > > select grp, max(id) as maxid > > from tbl > > group by grp > > having maxid = max(id) > > > > Regards, > > > > Peter Sap > > > > ----- Original Message ----- > > From: "Yves Goergen" <[EMAIL PROTECTED]> > > To: "List: MySQL" <[EMAIL PROTECTED]> > > Sent: Thursday, November 20, 2003 11:28 PM > > Subject: How to 'customize' GROUP BY? > > > > > > > Hi again... > > > yet another question to this list that maybe someone can easily answer > > me... > > > > > > When I do a GROUP BY on a column of my query, I'll get one random row > from > > > the entire group. But how can I tell MySQL to, i.e., give me the row > with > > > the highest value of another column or so? I mean something like > > > > > > SELECT id, grp FROM tbl GROUP BY grp ORDER BY id > > > > > > but with the ORDER BY relating to the GROUP... I don't know how to > express > > > this in SQL since it doesn't seem to be possible? > > > > > > -- > > > Yves Goergen > > > [EMAIL PROTECTED] > > > Please don't CC me (causes double mails) > > > -- 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]
