Well I think this is mostly working. I have a 'NULL' user ID which is 'system' that I need to get into here, but I think I'm mostly on track...
There are lots of ways to accomplish this task it seems. ALL of which would be so much easier if mySQL would just return all the data from the same MAX() row it just pulled. I really don't see why this has to be so complicated. I appreciate in some ways that mySQL tries to be "nice" and give me some data, but what good is it if it's WRONG?! Either throw an error, so I make a proper query, or else give me what I wanted... *sigh* Is there EVER a time when someone wants the behaviour of mySQL now? I can't for the life of me think of a case. This page had some more info: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Alex was close on his guess: mysql> select max(logs.created_on), username , max(logs.id),body from logs, users where logs.user_id=users.id group by user_id; +----------------------+----------+--------------+-----------------+ | max(logs.created_on) | username | max(logs.id) | body | +----------------------+----------+--------------+-----------------+ | 2006-04-27 23:34:32 | joe | 473 | Created Account | | 2006-04-27 22:18:35 | bob | 431 | Logged in. | +----------------------+----------+--------------+-----------------+ But notice that the body is now wrong. Grr... So, you have to MAX() that too... mysql> select max(logs.created_on), username , max(logs.id), max(body) from logs, users where logs.user_id=users.id group by user_id; +----------------------+----------+--------------+--------------------+ | max(logs.created_on) | username | max(logs.id) | max(body) | +----------------------+----------+--------------+--------------------+ | 2006-04-27 23:34:32 | joe | 473 | Viewed Users Stats | | 2006-04-27 22:18:35 | bob | 431 | Viewed Users Stats | +----------------------+----------+--------------+--------------------+ mysql> select * from `logs` l1 where id = (select max(l2.id) from logs l2 where l1.user_id = l2.user_id); +-----+---------+---------------------+--------------------+ | id | user_id | created_on | body | +-----+---------+---------------------+--------------------+ | 431 | 2 | 2006-04-27 22:18:35 | Viewed Users Stats | | 473 | 1 | 2006-04-27 23:34:32 | Viewed Users Stats | +-----+---------+---------------------+--------------------+ mysql> select l1.*, username from `logs` l1 join users on l1.user_id = users.id where l1.id = (select max(l2.id) from logs l2 where l1.user_id = l2.user_id); +-----+---------+---------------------+--------------------+----------+ | id | user_id | created_on | body | username | +-----+---------+---------------------+--------------------+----------+ | 473 | 1 | 2006-04-27 23:34:32 | Viewed Users Stats | joe | | 431 | 2 | 2006-04-27 22:18:35 | Viewed Users Stats | bob | +-----+---------+---------------------+--------------------+----------+ ________________________________ From: Alex Arul [mailto:[EMAIL PROTECTED] Sent: Friday, April 28, 2006 2:18 AM To: Daevid Vincent Subject: Re: Help with subqueries... MAX() and GROUP BY from what i understand, i think this is what you are looking for select max(created_on), username , max(logs.id),body from logs, users where logs.user_id=users.id group by user_id; while using subqueries that feed into where clauses, you can use "=" only for cases where the subquery returns only one value else you have to use "in". Thanx Alex On 4/28/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: > 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 I just noticed something kinda sorta unrelated... Maybe I don't even need a subquery at all (not that this example here solves the data being wrong issue)... SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs` LEFT JOIN users ON `logs`.user_id = users.id GROUP BY user_id ORDER BY `logs`.created_on; *************************** 1. row *************************** max(`logs`.created_on): 2006-04-25 20:10:59 id: 4 user_id: NULL created_on: 2006-03-13 18:40:39 body: TEST username: NULL *************************** 2. 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 *************************** 3. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
