http://jan.kneschke.de/projects/mysql/groupwise-max
2008/10/14 Peter Brawley <[EMAIL PROTECTED]> > 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, [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) >> >> >> ------------------------------------------------------------------------ >> >> >> 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/
