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/[EMAIL PROTECTED]
