MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.5.13-MariaDB


CREATE TABLE `loop_msg` (
  `msg_id` int(11) NOT NULL,
  `content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `loop_msg` (`msg_id`, `content`) VALUES
(2, 'Content 2'),
(3, 'Content 3'),
(4, 'Content 4'),
(6, 'Content 6'),
(7, 'Content 7'),
(8, 'Content 8');

CREATE TABLE `loop_msg_status` (
  `channel_id` bigint(20) NOT NULL,
  `msg_id` int(11) NOT NULL DEFAULT 0,
  `inbetween` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `loop_msg_status` (`channel_id`, `msg_id`, `inbetween`) VALUES
(123456789012345671, 2, 10),
(123456789012345672, 4, 30),
(123456789012345673, 6, 10),
(123456789012345674, 6, 0),
(123456789012345675, 6, 15),
(123456789012345676, 8, 10);

ALTER TABLE `loop_msg`
  ADD PRIMARY KEY (`msg_id`);

ALTER TABLE `loop_msg_status`
  ADD PRIMARY KEY (`channel_id`);

ALTER TABLE `loop_msg`
  MODIFY `msg_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;
WITH cte AS (
        SELECT loop_msg.msg_id
             , channel_id
             , inbetween
             , COALESCE(
                           LEAD(loop_msg.msg_id)        OVER w1
                         , FIRST_VALUE(loop_msg.msg_id) OVER w1
                       ) AS msg_id_next
             , COALESCE(
                           LEAD(content)        OVER w1
                         , FIRST_VALUE(content) OVER w1
                       ) AS content_next
          FROM      loop_msg
          LEFT JOIN loop_msg_status
            ON loop_msg.msg_id = loop_msg_status.msg_id
          WINDOW w1 AS (ORDER BY loop_msg.msg_id)
     )
SELECT channel_id, content_next, msg_id, msg_id_next
  FROM cte
 WHERE channel_id IS NOT NULL AND inbetween >= 10
;


channel_id
content_next
msg_id
msg_id_next


123456789012345671
Content 3
2
3

123456789012345672
Content 6
4
6

123456789012345673
Content 6
6
6

123456789012345675
Content 7
6
7

123456789012345676
Content 2
8
2

channel_id	content_next	msg_id	msg_id_next
123456789012345671	Content 3	2	3
123456789012345672	Content 6	4	6
123456789012345673	Content 6	6	6
123456789012345675	Content 7	6	7
123456789012345676	Content 2	8	2