MariaDB 10.6

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

select @@version;


@@version


10.6.7-MariaDB


CREATE TABLE loop_msg ( msg_id  int  auto_increment primary key, content varchar(20) );
INSERT INTO loop_msg (content) VALUES
   ('c1'), ('c2'), ('c3'), ('c4'), ('c5'), ('c6'), ('c7')
;
SELECT *
     , ROW_NUMBER() OVER (ORDER BY msg_id ASC) as rankx
     , COALESCE(
                   LEAD(msg_id)        OVER (ORDER BY msg_id ASC)
                 , FIRST_VALUE(msg_id) OVER (ORDER BY msg_id ASC)
               ) AS msgid_next
     , COALESCE(
                   LEAD(content)        OVER (ORDER BY msg_id ASC)
                 , FIRST_VALUE(content) OVER (ORDER BY msg_id ASC)
               ) AS content_next
  FROM loop_msg
;


msg_id
content
rankx
msgid_next
content_next


1
c1
1
2
c2

2
c2
2
3
c3

3
c3
3
4
c4

4
c4
4
5
c5

5
c5
5
6
c6

6
c6
6
7
c7

7
c7
7
1
c1


DROP TABLE IF EXISTS loop_msg;

CREATE TABLE loop_msg (
      msg_id  int  auto_increment primary key
    , chan    varchar(20)
    , content varchar(20)
);

INSERT INTO loop_msg (content, chan) VALUES
   ('c1', 'chan1')
 , ('c2', 'chan1')
 , ('c3', 'chan1')
 , ('c4', 'chan1')
 , ('c5', 'chan1')
 , ('c6', 'chan1')
 , ('c7', 'chan1')
 , ('d2', 'chan2')
 , ('d3', 'chan2')
 , ('d4', 'chan2')
 , ('d5', 'chan2')
 , ('d6', 'chan2')
 , ('d7', 'chan2')
 , ('d8', 'chan2')
;

SELECT *
     , ROW_NUMBER() OVER w1 as rankx
     , COALESCE(
                   LEAD(msg_id)        OVER w1
                 , FIRST_VALUE(msg_id) OVER w1
               ) AS msgid_next
     , COALESCE(
                   LEAD(content)        OVER w1
                 , FIRST_VALUE(content) OVER w1
               ) AS content_next
  FROM loop_msg
  WINDOW w1 AS (PARTITION BY chan ORDER BY msg_id)
;


msg_id
chan
content
rankx
msgid_next
content_next


1
chan1
c1
1
2
c2

2
chan1
c2
2
3
c3

3
chan1
c3
3
4
c4

4
chan1
c4
4
5
c5

5
chan1
c5
5
6
c6

6
chan1
c6
6
7
c7

7
chan1
c7
7
1
c1

8
chan2
d2
1
9
d3

9
chan2
d3
2
10
d4

10
chan2
d4
3
11
d5

11
chan2
d5
4
12
d6

12
chan2
d6
5
13
d7

13
chan2
d7
6
14
d8

14
chan2
d8
7
8
d2

msg_id	chan	content	rankx	msgid_next	content_next
1	chan1	c1	1	2	c2
2	chan1	c2	2	3	c3
3	chan1	c3	3	4	c4
4	chan1	c4	4	5	c5
5	chan1	c5	5	6	c6
6	chan1	c6	6	7	c7
7	chan1	c7	7	1	c1
8	chan2	d2	1	9	d3
9	chan2	d3	2	10	d4
10	chan2	d4	3	11	d5
11	chan2	d5	4	12	d6
12	chan2	d6	5	13	d7
13	chan2	d7	6	14	d8
14	chan2	d8	7	8	d2