From: Date: October 5 2007 2:10pm
Subject: Re: LIMIT within GROUP BY
List-Archive: http://lists.mysql.com/mysql/209405
Message-Id: <470629B4.7060903@xaprb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Change the > to >= and the < to <= to deal with this.

Baron

Miroslav Monkevic wrote:
> Thanks Baron, great advice (as always).
> 
> My real query is a bit more complicated but speaking in terms of example 
> I provided, I took this path:
> 
> ####################
> create table results
> (
>  person_id int(11),
>  points int(11)
> );
> 
> insert into results values(1, 34);
> insert into results values(1, 33);
> insert into results values(1, 33);
> insert into results values(1, 33);
> insert into results values(2, 49);
> insert into results values(2, 37);
> insert into results values(2, 46);
> insert into results values(2, 27);
> insert into results values(3, 42);
> insert into results values(3, 24);
> insert into results values(3, 35);
> insert into results values(3, 18);
> 
> SELECT points
> FROM results
> WHERE
> (
>  SELECT count(*)
>  FROM results as R
>  WHERE R.person_id = results.person_id AND R.points > results.points
> ) <3
> ORDER BY person_id, points DESC
> 
> 
> person_id    points
>  1                 34
>  1                 33
>  1                 33
>  1                 33
>  2                 49
>  2                 46
>  2                 37
>  3                 42
>  3                 35
>  3                 24
> 
> ####################
> 
> 
> As you can see limiting does not work if there are record with the same 
> amount of points. I haven't found any solution yet.
> 
> 
> 
> Baron Schwartz wrote:
>> Hi,
>>
>> Miroslav Monkevic wrote:
>>> Hello,
>>>
>>> MySQL 4.1
>>>
>>> I have query:
>>> SELECT SUM(points) as ranking FROM results GROUP BY person_id ORDER 
>>> BY ranking DESC
>>>
>>> My goal is to sum 7 greatest results for each person.
>>>
>>> In more general, my question is: is there a way to limit number of 
>>> records within  groups in  "group by" query.
>>
>> Try this:
>> http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ 
>>
>>
>> Cheers
>> Baron
>>
>>
> 
>