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

I tried.

Then I get:

###############
person_id    points
1                 34
2                 49
2                 46
2                 37
3                 42
3                 35
3                 24
################

instead of desired:

################
person_id    points
1                 34
1                 33
1                 33
2                 49
2                 46
2                 37
3                 42
3                 35
3                 24
################

I've been playing with JOIN also. It does not work because of the same 
problem:

################

SELECT R1.person_id, R1.points, COUNT(*) AS higher
FROM results AS R1
JOIN results AS R2 ON R1.person_id=R2.person_id AND R1.points <= R2.points
GROUP BY R1.person_id, R1.points
HAVING higher <=3
ORDER BY R1.person_id, R1.points DESC


person_id         points         higher
 1                       34               1
 2                       49               1
 2                       46               2
 2                       37               3
 3                       42               1
 3                       35               2
 3                       24               3

###################



Baron Schwartz wrote:
> 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
>>>
>>>
>>
>>
>
>
>


-- 
Best regards,
Miroslav Monkevic