List:General Discussion« Previous MessageNext Message »
From:Pat Adams Date:April 28 2006 8:38pm
Subject:RE: Help with subqueries... MAX() and GROUP BY
View as plain text  
On Fri, 2006-04-28 at 00:13 -0700, Daevid Vincent wrote:
> 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...

If you don't include id in either an aggregate function or the GROUP BY
clause, MySQL 'helps' you by choosing a seemingly-random value to stick
in the id field. 

-- 
Pat Adams
Digital Darkness Promotions
Check out the Dallas Music Wiki http://digitaldarkness.com/tiki

Attachment: [application/pgp-signature] This is a digitally signed message part signature.asc
Attachment: [application/pgp-signature] This is a digitally signed message part signature.asc
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