codice:
DROP TABLE IF EXISTS `vendite`;
CREATE TABLE `vendite` (
`id` int(11) NOT NULL auto_increment,
`id_cliente` int(11) default NULL,
`acquisto` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `vendite` */
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (1,1,'2001-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (2,1,'1989-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (3,1,'2001-02-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (4,2,'1989-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (5,2,'1987-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (6,3,'1989-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (7,3,'2000-10-10');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (8,4,'1986-01-01');
insert into `vendite` (`id`,`id_cliente`,`acquisto`) values (9,4,'1985-01-01');
select *
from
(select concat(id_cliente,max(acquisto)) as recente from vendite
group by id_cliente) as tab1
left join vendite as tab2
ON tab1.recente = concat(tab2.id_cliente,tab2.acquisto)
where tab2.acquisto < '1990-01-01'