From: Peter Brawley
Date: October 14 2008 1:42pm
Subject: Re: Problem with GROUP BY
List-Archive: http://lists.mysql.com/mysql/214877
Message-Id: <48F4A1B5.6050308@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010102030004090101060704"

--------------010102030004090101060704
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>   

--------------010102030004090101060704--