> 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]
