By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `LOGTABLE` (
`ID` bigint(20) NOT NULL,
`ITEM_ID` int(11) NOT NULL,
`DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`TYPE` tinyint(4) NOT NULL,
`NOTE` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `LOGTABLE`
MODIFY `ID` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;
INSERT INTO `LOGTABLE` (`ID`, `ITEM_ID`, `DATETIME`, `TYPE`, `NOTE`) VALUES
(2, 167, '2021-06-15 16:47:23', 0, 'T'),
(3, 168, '2021-06-15 16:48:11', 0, 'F'),
(4, 167, '2021-06-15 16:59:51', 0, 'T'),
(5, 168, '2021-06-15 17:00:48', 0, 'F'),
(6, 167, '2021-06-15 17:12:19', 0, 'T'),
(7, 168, '2021-06-15 17:13:25', 0, 'T'),
(8, 167, '2021-06-15 17:24:47', 0, 'F'),
(9, 168, '2021-06-15 17:26:02', 0, 'F'),
(10, 167, '2021-06-15 17:37:15', 0, 'T');
select *
from (select *, case when NOTE <> LAG(NOTE,1,'X') over (partition by item_id order by datetime) then 1 else 0 end lnote
from LOGTABLE
) t
where lnote = 1
| ID | ITEM_ID | DATETIME | TYPE | NOTE | lnote |
|---|---|---|---|---|---|
| 2 | 167 | 2021-06-15 16:47:23 | 0 | T | 1 |
| 8 | 167 | 2021-06-15 17:24:47 | 0 | F | 1 |
| 10 | 167 | 2021-06-15 17:37:15 | 0 | T | 1 |
| 3 | 168 | 2021-06-15 16:48:11 | 0 | F | 1 |
| 7 | 168 | 2021-06-15 17:13:25 | 0 | T | 1 |
| 9 | 168 | 2021-06-15 17:26:02 | 0 | F | 1 |
select item_id, GROUP_CONCAT(ID) Ids
from (select *, case when NOTE <> LAG(NOTE,1,'X') over (partition by item_id order by datetime) then 1 else 0 end lnote
from LOGTABLE
) t
where lnote = 1
group by item_id
| item_id | Ids |
|---|---|
| 167 | 2,8,10 |
| 168 | 3,7,9 |