"Jay Paulson \(CE CEN\)" <[EMAIL PROTECTED]> wrote on 12/28/2005 02:37:36 PM:
> My query below returns however many rows fit the WHERE condition, in > this case when they year, period, week is <= 2009131. In my case it > is returning 11 rows because I have 11 rows where the year,period, > week is 2006XXX. However, this is not what I want. I only need 4 > rows returned to me and not all 11 no matter what the <= XXXXXXX > part of the where is. > > I guess my question becomes is it possible to put a LIMIT of how > many rows are returned on a GROUP BY? > > Thanks! > > SELECT ROUND(AVG(page_hit)) as page_hit, > ROUND(AVG(training_tracking)) as training_tracking, > ROUND(AVG(certificates)) as certificates, ROUND(AVG(team_members)) > as team_members, ROUND(AVG(evaluation)) as evaluation, CONCAT(year, > period,week) as date > > FROM statistics > > WHERE CONCAT(year,period,week) <= 2009131 AND region_id != 'AA' > > GROUP BY date > > ORDER BY date DESC LIMIT 0,77 The only way I have been able to do groupwise limits has been to save the grouped query into an intermediate table setup with a multi-column PK and an auto_increment column. That way I can serialize each member of each group. Then all I need to do is to select from my intermediate table where serno<5 and I have my 4 per group. If you want to be fancy, the intermediate table can also be a temporary table so that one user session doesn't clobber another's report. I don't think there is any way with plain-old SQL (extended or otherwise) to do it in a single statement (unless you are doing it iteratively - that is: in a stored procedure and row-by-row). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
