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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
