RE: Bug in GROUP BY/CASE/MAX? Well, according to my reading/understanding of SQL ANSI'92, MAX/MIN/SUM/AVG are supposed to eliminate the NULLs from the set before doing the appropriate calculation...
Bob Diss, [EMAIL PROTECTED] =================================================================== >Date: Tue, 10 Dec 2002 15:45:03 -0600 >From: gerald_clark <[EMAIL PROTECTED]> >To: "'Bob Diss'" <[EMAIL PROTECTED]> > >What is the MAX of a known and an unknown value? >It would be unknown or NULL > >'Bob Diss' wrote: > >>RE: Bug in GROUP BY/CASE/MAX? >> >>More info -- it seems that if I change the 'NULL' to '' >>(i.e. the empty string) in the CASE statements, I get the >>result I'm looking for. That is, the query: >> >>mysql> select max(case when col = 1 then val else '' end) as color >> from t group by row; >> >>returns the three rows 'orange', 'yellow', and 'green'. >> >>Why is that? Is something broken when MySQL calculates the >>aggregate for a column that contains a NULL value? Is there a doc >>to explain how an aggregate function should work in the presence >>of NULL? >> >>Bob Diss, [EMAIL PROTECTED] >> >>=================================================================== >> >> >> >>>Date: Tue, 10 Dec 02 13:57:00 EST >>>From: "'Bob Diss'" <[EMAIL PROTECTED]> >>>To: "'MySQL List'" <[EMAIL PROTECTED]> >>> >>>Bug in GROUP BY/CASE/MAX? >>> >>>I'm seeing a strange result when I group rows and using CASE and >>>MAX() to select the column I'm interested in (typical pivot-table >>>operation). Here's my sample case: >>> >>>mysql> -- create table >>>mysql> create table t (row int not null, >>> col int not null, >>> val varchar(255) not null); >>> >>>mysql> -- populate with test records >>>mysql> insert into t values (1,1,'orange'); >>>mysql> insert into t values (1,2,'large'); >>>mysql> insert into t values (2,1,'yellow'); >>>mysql> insert into t values (2,2,'medium'); >>>mysql> insert into t values (3,1,'green'); >>>mysql> insert into t values (3,2,'small'); >>> >>>mysql> -- group by row, extract values where col=1 >>>mysql> select max(case when col = 1 then val else null end) as color >>> from t group by row; >>> >>>MySQL shows the result set as: >>> >>>+--------+ >>>| color | >>>+--------+ >>>| orange | >>>| NULL | >>>| NULL | >>>+--------+ >>>3 rows in set (0.00 sec) >>> >>>I would have expected MySQL to group the records into >>>three sets according to row: >>> (1,1,'orange') >>> (1,2,'large') >>> >>> (2,1,'yellow') >>> (2,2,'medium') >>> >>> (3,1,'green') >>> (3,2,'small') >>> >>>then, for each set, apply the case expression: >>> (1,1,'orange') becomes 'orange' >>> (1,2,'large') becomes NULL >>> >>> (2,1,'yellow') becomes 'yellow' >>> (2,2,'medium') becomes NULL >>> >>> (3,1,'green') becomes 'green' >>> (3,2,'small') becomes NULL >>> >>>then, apply the max() aggregate: >>> max('orange',NULL) becomes 'orange' >>> max('yellow',NULL) becomes 'yellow' >>> max('green',NULL) becomes 'green' >>> >>>So, I'm expecting to see a result set of three colors. This query >>>works as I expect in MS-SQLServer 7.0. Why doesn't it work the same >>>way in MySQL? >>> >>>I'm running the RPMS from MySQL.com for MySQL-max 3.23.52 >>>on RedHat 7.3. >>> >>>tia, >>> >>>Bob Diss, [EMAIL PROTECTED] >>> >>>--------------------------------------------------------------------- >>>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 >>> >>> >>> >> >>--------------------------------------------------------------------- >>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 <mysql-unsubscribe-gerald_clark=suppliersystems.com- >@lists.mysql.com> >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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