Dmitry, ----- Original Message ----- From: "Dmitry Kosoy" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, December 15, 2002 7:11 PM Subject: RE: Group by problem
> Hi, > > I checked it with 3.23.54 on Windows > and got the same incorrect results. ... > > The following sql operators caused to incorrect results: > > > > CREATE TEMPORARY TABLE temp_list ( > > CurrID char(42) NOT NULL, > > OriginalID char(42) NOT NULL , > > DocRevision int(16) unsigned NOT NULL > > ) TYPE=InnoDB; > > > > insert into temp_list > > select CurrID, OriginalID, max(DocRevision) > > from some_table > > where DocRevision in (0,1,2,3) > > group by OriginalID > > having (sum(deleted) = 0); > > > > I revealed that temp_list table contains some incorrect values of CurrID > > (not matched to found OriginaID and Docrevision). > > According to Mysql documentation it should work. The values of CurrID is a > > primary key in some_table. since you do not mention CurrID in the GROUP BY list, MySQL will return SOME value for it in the group. The manual does not say that the returned CurrID should necessarily be from the same row as Docrevision. But below you see a CONCAT trick which you can use to get a matching pair of DocRevision and CurrID. http://www.mysql.com/doc/en/Group_by_functions.html " MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. ... Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results. In some cases, you can use MIN() and MAX() to obtain a specific column value even if it isn't unique. The following gives the value of column from the row containing the smallest value in the sort column: SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7) " > > Regards, > > Dmitry Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
