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