"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
>
> 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
