List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 28 2006 7:13am
Subject:RE: Help with subqueries... MAX() and GROUP BY
View as plain text  

> -----Original Message-----
> From: Alex Arul [mailto:alex.lurthu@stripped] 
> Sent: Thursday, April 27, 2006 11:28 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: Help with subqueries...
> 
> On 4/28/06, Daevid Vincent <daevid@stripped> wrote:
> >
> > vmware reviewit # mysql --version
> > mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) 
> using readline
> > 5.1
> >
> > Given two tables:
> >
> > CREATE TABLE `logs` (
> >           `id` bigint(20) unsigned NOT NULL auto_increment,
> >           `user_id` int(10) unsigned default '0',
> >           `created_on` timestamp NOT NULL default
> >                         CURRENT_TIMESTAMP on update 
> CURRENT_TIMESTAMP,
> >           `body` text,
> >           PRIMARY KEY  (`id`),
> >         ) ENGINE=InnoDB;
> >
> > CREATE TABLE `users` (
> >           `id` int(10) unsigned NOT NULL auto_increment,
> >           `username` varchar(30) NOT NULL,
> >           `firstname` varchar(255) NOT NULL,
> >           `lastname` varchar(255) NOT NULL,
> >           `login_date` datetime default NULL,
> >           `timestamp` timestamp NOT NULL default
> >                         CURRENT_TIMESTAMP on update 
> CURRENT_TIMESTAMP,
> >           PRIMARY KEY  (`id`),
> >         ) ENGINE=InnoDB;
> >
> >
> > I want a query that will show me the username and the 
> single most current
> > log body and date for every user.
> >
> > I think I need to use a sub query, but I can't even get the 
> very basic
> > example one to work...
> > http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
> >
> > mysql> SELECT * FROM logs WHERE user_id = (SELECT id FROM users);
> > ERROR 1242 (21000): Subquery returns more than 1 row
> 
> 
> try SELECT * FROM logs WHERE user_id in (SELECT id FROM users);
> 
> mysql> SELECT logs.*, users.username
> >     -> FROM logs JOIN users ON users.id = logs.id
> >     -> WHERE user_id = (SELECT id FROM users ORDER BY username);
> > ERROR 1242 (21000): Subquery returns more than 1 row
> 
> 
> again replace = by in.
> 
> Hope this helps.

Thanks Alex, that got me started. I don't understand why I had to use "IN"
when the example uses "=" but at least it kinda works...

The problem is now that I can't get the right data. 

mysql> select max(created_on), user_id, id from logs group by user_id;
+---------------------+---------+----+
| max(created_on)     | user_id | id |
+---------------------+---------+----+
| 2006-04-25 20:10:59 | NULL    | 4  |
| 2006-04-27 23:48:27 | 1       | 50 |  <-- 456
| 2006-04-27 22:18:35 | 2       | 16 |  <-- 431
+---------------------+---------+----+
The "max" date is correct but that isn't the correspoinding action id, 
they should be 456 and 431 instead...

Which I really don't understand this:

mysql> select max(id), user_id, id from logs group by user_id;
+---------+---------+----+
| max(id) | user_id | id |
+---------+---------+----+
| 183     | NULL    | 4  |
| 456     | 1       | 50 |
| 431     | 2       | 16 |
+---------+---------+----+
3 rows in set (0.00 sec)

So that is making this query wrong too of course.

mysql> SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs`
LEFT JOIN users ON `logs`.user_id = users.id WHERE user_id IN (SELECT id
FROM users ORDER BY username)  GROUP BY user_id ORDER BY `logs`.created_on\G
*************************** 1. row ***************************
max(`logs`.created_on): 2006-04-27 22:18:35
                    id: 16
               user_id: 2
            created_on: 2006-03-14 22:40:16
                  body: Logged in.
              username: joe
*************************** 2. row ***************************
max(`logs`.created_on): 2006-04-27 23:48:27
                    id: 50
               user_id: 1
            created_on: 2006-03-31 16:15:16
                  body: Created Account
              username: bob


Thread
Help with subqueries...Daevid Vincent28 Apr
Re: Help with subqueries...Alex Arul28 Apr
  • RE: Help with subqueries... MAX() and GROUP BYDaevid Vincent28 Apr
RE: Help with subqueries... MAX() and GROUP BYDaevid Vincent28 Apr
RE: Help with subqueries... MAX() and GROUP BYPat Adams28 Apr