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 <[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 <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
