List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 28 2006 8:14pm
Subject:RE: Help with subqueries... MAX() and GROUP BY [sovled]
View as plain text  
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:alex.lurthu@stripped] 
	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 <daevid@stripped> 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
		

		
		



Thread
RE: Help with subqueries... MAX() and GROUP BY [sovled]Daevid Vincent28 Apr
RE: Help with subqueries... MAX() and GROUP BY [sovled]Shawn Green29 Apr