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, rld@stripped
>
>===================================================================
>
>
>
>>Date: Tue, 10 Dec 02 13:57:00 EST
>>From: "'Bob Diss'" <RLD@stripped>
>>To: "'MySQL List'" <mysql@stripped>
>>
>>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, rld@stripped
>>
>>---------------------------------------------------------------------
>>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 <mysql-thread127138@stripped>
>>To unsubscribe, e-mail <mysql-unsubscribe-RLD=opn.com@stripped>
>>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 <mysql-thread127157@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
