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
