List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 28 2006 7:22am
Subject:RE: Help with subqueries... MAX() and GROUP BY
View as plain text  
> 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

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