By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table pins (
Pin int,
Device varchar(10),
`DateTime` datetime
);
insert into pins values
(1, 'A-in', '2023-01-01 10:00:00'),
(1, 'B-Out', '2023-01-01 10:30:00'),
(2, 'B-in', '2023-01-01 11:00:00'),
(2, 'A-Out', '2023-01-01 11:30:00'),
(3, 'C-In', '2023-01-01 13:00:00');
select *
from pins p
where p.Device like '%-in' and not exists (
select *
from pins o
where trim(trailing '-Out' from o.Device)=trim(trailing '-in' from p.Device) and o.Device like '%-Out'
and o.DateTime>=p.DateTime
);
Records: 5 Duplicates: 0 Warnings: 0
| Pin | Device | DateTime |
|---|---|---|
| 2 | B-in | 2023-01-01 11:00:00 |
| 3 | C-In | 2023-01-01 13:00:00 |