From: Peter Brawley Date: December 28 2005 8:27pm Subject: Re: LIMIT on GROUP BY? List-Archive: http://lists.mysql.com/mysql/193297 Message-Id: <43B2F548.5030106@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43B2F5496252=======" --=======AVGMAIL-43B2F5496252======= Content-Type: multipart/alternative; boundary=------------030700040702000806030703 --------------030700040702000806030703 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jay, />I only need 4 rows returned to me and not all 11 no >matter what the <= XXXXXXX part of the where is. / ... LIMIT BY 0, 4? PB ----- Jay Paulson (CE CEN) wrote: >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 > > > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Free Edition. >Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date: 12/27/2005 > > --------------030700040702000806030703 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jay,

>I only need 4 rows returned to me and not all 11 no
>matter what the <= XXXXXXX part of the where is.

... LIMIT BY 0, 4?

PB

-----

Jay Paulson (CE CEN) wrote:

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


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date: 12/27/2005

--------------030700040702000806030703-- --=======AVGMAIL-43B2F5496252======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date: 12/27/2005 --=======AVGMAIL-43B2F5496252=======--