RE: Help with subqueries... MAX() and GROUP BY [sovled]
--- Daevid Vincent <[EMAIL PROTECTED]> wrote: > 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* I agree, it should throw an error. > 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. When it comes to this particular behavior, I agree that it is flawed. However, there is WAY too much code out there to just turn it off. This is definitely a migration/training issue as well as a parser issue now. > This page had some more info: > http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html > I am so glad the fine manual was useful for you. I think that everyone should review the techniques discussed not just in the article you quoted but also in the articles around it. Shawn Green Database Administrator __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with subqueries... MAX() and GROUP BY
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 signature.asc Description: This is a digitally signed message part
RE: Help with subqueries... MAX() and GROUP BY [sovled]
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 4
RE: Help with subqueries... MAX() and GROUP BY
> 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]
RE: Help with subqueries... MAX() and GROUP BY
> -Original Message- > From: Alex Arul [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 27, 2006 11:28 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: Re: Help with subqueries... > > On 4/28/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: > > > > vmware reviewit # mysql --version > > mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) > using readline > > 5.1 > > > > Given two tables: > > > > CREATE TABLE `logs` ( > > `id` bigint(20) unsigned NOT NULL auto_increment, > > `user_id` int(10) unsigned default '0', > > `created_on` timestamp NOT NULL default > > CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP, > > `body` text, > > PRIMARY KEY (`id`), > > ) ENGINE=InnoDB; > > > > CREATE TABLE `users` ( > > `id` int(10) unsigned NOT NULL auto_increment, > > `username` varchar(30) NOT NULL, > > `firstname` varchar(255) NOT NULL, > > `lastname` varchar(255) NOT NULL, > > `login_date` datetime default NULL, > > `timestamp` timestamp NOT NULL default > > CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP, > > PRIMARY KEY (`id`), > > ) ENGINE=InnoDB; > > > > > > I want a query that will show me the username and the > single most current > > log body and date for every user. > > > > I think I need to use a sub query, but I can't even get the > very basic > > example one to work... > > http://dev.mysql.com/doc/refman/5.0/en/subqueries.html > > > > mysql> SELECT * FROM logs WHERE user_id = (SELECT id FROM users); > > ERROR 1242 (21000): Subquery returns more than 1 row > > > try SELECT * FROM logs WHERE user_id in (SELECT id FROM users); > > mysql> SELECT logs.*, users.username > > -> FROM logs JOIN users ON users.id = logs.id > > -> WHERE user_id = (SELECT id FROM users ORDER BY username); > > ERROR 1242 (21000): Subquery returns more than 1 row > > > again replace = by in. > > Hope this helps. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
