aggregate function
Hello, I have question to SQL-standard on MySQL:
-- Dump:
CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`fld_common` int(11) default NULL,
`status` int(11) default NULL,
`date_fld` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `fld_common` (`fld_common`)
);
insert into `table1` values
(1,1,3,'2006-05-12'),
(2,2,2,'2006-05-11'),
(3,3,3,'2006-05-14');
CREATE TABLE `table2` (
`id` int(11) NOT NULL,
`fld_common` int(11) default NULL,
`status` int(11) default NULL,
`date_fld` date default NULL,
PRIMARY KEY (`id`),
KEY `fld_common` (`fld_common`)
);
insert into `table2` values
(1,1,2,'2006-05-11'),
(2,1,2,'2006-05-10'),
(3,1,1,'2006-05-06'),
(4,2,2,'2006-05-08'),
(5,2,1,'2006-05-01'),
(6,3,2,'2006-05-13'),
(7,3,2,'2006-05-12'),
(8,3,1,'2006-05-11'),
(9,3,3,'2006-05-09');
-- Query:
select
t1.fld_common,
t1.status,
t1.date_fld,
t2.status pre_status,
max(t2.date_fld) pre_date_fld
from
table2 t2
left join
table1 t1
on
t2.fld_common = t1.fld_common
where
t1.status != t2.status
group by
t2.fld_common
Why this query works on MySQL?
MSSQL and Oracle give error: "...aggregate function or the GROUP BY clause"