[This is an email copy of a Usenet post to "mailing.database.mysql"] Martin, Here is how you can do what you want using a temporary table. Note that using this method, if you have two guys who play the same position, and they have an equal number of points, it'll select both records. mysql> CREATE TABLE Player (id INT NOT NULL, name CHAR(24) NOT NULL, pos CHAR(2) NOT NULL, pts INT NOT NULL, PRIMARY KEY (id)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO Player VALUES (1, "John", "LW", 12), (2, "Jim", "RW", 18), (3, "Pete", "LW", 30), (4, "Sam", "C", 14), (5, "Larry", "RW", 25), (6, "Harry", "RW", 43), (7, "Mike", "C", 4), (8, "Louise", "LW", 76); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> CREATE TEMPORARY TABLE t1 SELECT pos,MAX(pts) AS pts FROM Player GROUP BY pos; Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT Player.* FROM Player,t1 WHERE Player.pos=t1.pos AND Player.pts = t1.pts; +----+--------+-----+-----+ | id | name | pos | pts | +----+--------+-----+-----+ | 4 | Sam | C | 14 | | 8 | Louise | LW | 76 | | 6 | Harry | RW | 43 | +----+--------+-----+-----+ 3 rows in set (0.03 sec) -- Jeff S Wheeler [EMAIL PROTECTED] Software Development Five Elements, Inc http://www.five-elements.com/~jsw/ database sql query table In article <[EMAIL PROTECTED]>, "Martin Boudreau" <[EMAIL PROTECTED]> wrote: > Hello there, > > I have a problem making a simple SQL query > > Let's say I have a table of hockey players. -------------------------- > |ID|NAME |POS|PTS| > -------------------------- > |01|John Do | LW| 12| > |02|Jim Do | RW| 18| > |03|Pete Do | LW| 30| > |04|Sam Do | C| 14| > |05|Larry Do | RW| 25| > |06|Harry Do | RW| 43| > |07|Mike Do | C| 4| > |08|Louise Do | LW| 76| > -------------------------- > > And I would like to select the player witch has the mose PTS in each > POS. Something that would return > > -------------------------- > |ID|NAME |POS|PTS| > -------------------------- > |04|Sam Do | C| 14| > |06|Harry Do | RW| 43| > |08|Louise Do | LW| 76| > -------------------------- > > TIA > > Martin Boudreau --------------------------------------------------------------------- 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
