I created a table with,
CREATE TABLE info (
number INTEGER UNSIGNED,
event INTEGER UNSIGNED,
name VARCHAR(2000) NOT NULL,
PRIMARY KEY (number, event)
);
and populated it with data to produce this,
+--------+-------+-------+
| number | event | name |
+--------+-------+-------+
| 67 | 1 | Alice |
| 67 | 2 | Bob |
| 69 | 1 | Carol |
| 70 | 1 | Alex |
| 71 | 1 | David |
| 72 | 1 | Bob |
| 72 | 2 | Alice |
| 72 | 3 | David |
+--------+-------+-------+
What I want to produce is a table with rows from the original with only
the maximum value of event for each corresponding number selected, like
this
+--------+------------+-------+
| number | event | name |
+--------+------------+-------+
| 67 | 2 | Bob |
| 69 | 1 | Carol |
| 70 | 1 | Alex |
| 71 | 1 | David |
| 72 | 3 | David |
+--------+------------+-------+
The closest I have managed to produce using GROUP BY is,
mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
+--------+------------+-------+
| number | MAX(event) | name |
+--------+------------+-------+
| 67 | 2 | Alice | <- should be Bob
| 69 | 1 | Carol |
| 70 | 1 | Alex |
| 71 | 1 | David |
| 72 | 3 | Bob | <- should be David
+--------+------------+-------+
I tried using a HAVING clause but got nowhere.
Can anybody help please ?
TTFN,
Philip Riebold, [EMAIL PROTECTED] /"\
Media Services \ /
University College London X ASCII Ribbon Campaign
Windeyer Building, 46 Cleveland Street / \ Against HTML Mail
London, W1T 4JF
+44 (0)20 7679 9259 (switchboard), 09259 (internal)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
