I think that answer is (because I group on user field):
create temporary table usertempcnt
SELECT *,(@a:= IF(@b=user,@a+1,1)) as cnt , @b:=user from users order
by
user,points desc;
Thanks for advice on temporary table and great example!
Anzej
P.S. Sorry form misspelled subject: "nest" should be "best" :)
----- Original Message -----
From: "Richard Clarke" <[EMAIL PROTECTED]>
To: "Richard Clarke" <[EMAIL PROTECTED]>; "Anzej Becan"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, March 11, 2002 6:36 PM
Subject: Re: How to sum only nest N values?
> argh read your scheme wrong
> answer should be:
>
> create temporary table usertempcnt
> SELECT *,(@a:= IF(@b=id,@a+1,1)) as cnt, @b:=id from user order by
> user,points desc;
>
> select *,sum(points) from usertmpcnt where cnt<=2;
>
> where 2 is your N
>
> Regards,
> Richard
>
> ----- Original Message -----
> From: "Richard Clarke" <[EMAIL PROTECTED]>
> To: "Anzej Becan" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Monday, March 11, 2002 5:26 PM
> Subject: Re: How to sum only nest N values?
>
>
> > create temporary table usertempcnt
> > SELECT *,(@a:= IF(@b=id,@a+1,1)) as cnt, @b:=id from user order by
> > id,points desc;
> >
> > select *,sum(points) from usertmpcnt where cnt<=2;
> >
> > where 2 is your N
> >
> > Regards,
> > Richard
> >
> > ----- Original Message -----
> > From: "Anzej Becan" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, March 11, 2002 9:39 AM
> > Subject: Fw: How to sum only nest N values?
> >
> >
> > > I have table "users" with some results. Now I would like to sum up
> highest
> > > two points (not all!) for each user and sort by this sum.
> > > If I use this sql query...
> > > SELECT user, SUM(points) total FROM users GROUP BY user ORDER BY
total
> > > desc;
> > > ... the result is sum of all points for each user.
> > > How to sum only highest two (or N) points?
> > >
> > > Thanks for any advice!
> > > Anzej
> > >
> > >
> > > Structure and data:
> > >
> > > CREATE TABLE users (
> > > id tinyint(3) unsigned NOT NULL auto_increment,
> > > user char(5) NOT NULL default '',
> > > points tinyint(3) unsigned NOT NULL default '0',
> > > PRIMARY KEY (id),
> > > UNIQUE KEY id (id)
> > > ) TYPE=MyISAM;
> > > INSERT INTO users VALUES (1, 'user1', 10);
> > > INSERT INTO users VALUES (2, 'user1', 20);
> > > INSERT INTO users VALUES (3, 'user1', 50);
> > > INSERT INTO users VALUES (4, 'user1', 40);
> > > INSERT INTO users VALUES (5, 'user2', 10);
> > > INSERT INTO users VALUES (6, 'user2', 20);
> > > INSERT INTO users VALUES (7, 'user2', 50);
> > >
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
