Re: LIMIT within GROUP BY

[Miroslav Monkevic]

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