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