List:General Discussion« Previous MessageNext Message »
From:philip Date:October 14 2008 1:20pm
Subject:Problem with GROUP BY
View as plain text  
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)

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