List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 28 2005 8:28pm
Subject:Re: LIMIT on GROUP BY?
View as plain text  
"Jay Paulson \(CE CEN\)" <Jay.Paulson@stripped> 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 

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
LIMIT on GROUP BY?Jay Paulson \(CE CEN\)28 Dec
  • Re: LIMIT on GROUP BY?Peter Brawley28 Dec
  • Re: LIMIT on GROUP BY?SGreen28 Dec
  • Re: LIMIT on GROUP BY?Felix Geerinckx29 Dec