[color=red]( select max(`id`) as id from `Master_hist_zakaz` where `zakaz_id` in ( select `id` from `All_Zakaz` where `date`>='2020-03-21 00:00:00' and `date`<='2020-04-03 23:59:59' and `type_id`='9' ) group by `zakaz_id`,`master_id` )[/color]

select max(t2.`zakaz_id`)
from `All_Zakaz` t1 
JOIN `Master_hist_zakaz` t2 
  on t1.`id`=t2.`zakaz_id` 
where t1.`date`>='2020-03-21 00:00:00' and t1.`date`<='2020-04-03 23:59:59' and t1.`type_id`='9' 
group by t2.`zakaz_id`,t2.`master_id`

CREATE TABLE `Master_hist_zakaz` (
  `id` int(100) NOT NULL,
  `master_id` varchar(4000) NOT NULL,
  `zakaz_id` varchar(4000) NOT NULL,
  `status_id` varchar(4000) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Indexes for table `Master_hist_zakaz`
--
ALTER TABLE `Master_hist_zakaz`
  ADD PRIMARY KEY (`id`)
  ADD KEY `zakaz_id` (`zakaz_id`(1024)),
  ADD KEY `date` (`date`);



CREATE TABLE `All_Zakaz` (
  `id` int(11) NOT NULL,

  `master_id` varchar(100) NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_id` varchar(100) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `All_Zakaz`
  ADD PRIMARY KEY (`id`),
  ADD KEY `date` (`date`)