LIMIT within GROUP BY 2007-10-05 - By Miroslav Monkevic
Back
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
