LIMIT within GROUP BY 2007-10-05 - By Miroslav Monkevic
Back
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/mysql?unsub=mysql@(protected)
|
|
