From: Miroslav Monkevic (miroslavmonkevic.info)
Date: Fri Oct 05 2007 - 07:56:35 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]