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
