Philip
> mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
For discussion & examples see "Within-group aggregates" at
http://www.artfulsoftware.com/queries.php.
PB
-----
philip wrote:
> 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, p.riebold@stripped /"\
> 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)
>
>
>
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com
> Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
>
>
