List:General Discussion« Previous MessageNext Message »
From:Olexandr Melnyk Date:October 14 2008 1:55pm
Subject:Re: Problem with GROUP BY
View as plain text  
http://jan.kneschke.de/projects/mysql/groupwise-max

2008/10/14 Peter Brawley <peter.brawley@stripped>

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


-- 
--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

Thread
Why different engines in one database?hezjing12 Oct
  • Re: Why different engines in one database?D. Dante Lorenso12 Oct
    • Problem with GROUP BYphilip14 Oct
      • Re: Problem with GROUP BYPeter Brawley14 Oct
        • Re: Problem with GROUP BYOlexandr Melnyk14 Oct
          • Re: Problem with GROUP BYphilip15 Oct
            • Re: Problem with GROUP BYMoon's Father21 Oct
  • Re: Why different engines in one database?Simon J Mudd12 Oct