Select first rank if RANK()+1 overflows with JOINS

Question

I have a table called loop_msg with msg_id and content. I have a second table called loop_msg_status with channel and msg_id. This is used to post messages as round-robin in different channels, so I need to keep track of which msg_id has been posted last in each channel.

SELECT 
  a.msg_id, 
  b.content, 
  b.rank, 
  b.rank + 1, 
  c.rank, 
  c.content as next_content 
FROM 
  loop_msg_status as a 
  LEFT JOIN (
    SELECT 
      *, 
      RANK() OVER (
        ORDER BY 
          msg_id ASC
      ) as rank 
    FROM 
      loop_msg
  ) b ON a.msg_id = b.msg_id 
  LEFT JOIN (
    SELECT 
      *, 
      RANK() OVER (
        ORDER BY 
          msg_id ASC
      ) as rank 
    FROM 
      loop_msg
  ) c ON b.rank + 1 = c.rank

With this query I'm able to get the current msg_id and its content from every channel. I'm also getting that msg_id's rank from table loop_msg. I also get its rank+1 and get rank+1's content, if that makes sense. And it works. However, if rank is the highest one, then rank+1 doesn't exist and I get a NULL next_content. I would like in that case to SELECT the lowest rank from loop_msg which is 1 and get its content as next_content instead. Should I add a IF() and if so, where? Or is there a better way to do this?

SELECT version();
> 10.5.13-MariaDB

Full SQL example:

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` bigint(20) NOT NULL,
  `msg_id` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `loop_msg_status` (`channel`, `msg_id`) VALUES
(316757642527768577, 4),
(384071823261696010, 6),
(939746456632438804, 8);


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

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


ALTER TABLE `loop_msg`
  MODIFY `msg_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;

Expected result

channel            | nextContent
--------------------------------
316757642527768577 | Content 6
384071823261696010 | Content 7
939746456632438804 | Content 2

Answer 1

Final SQL with the schema (and expected result) given in the question:

The final fiddle

WITH cte AS (
        SELECT loop_msg.msg_id
             , channel
             , 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, content_next
  FROM cte
 WHERE channel IS NOT NULL
;

The result:

+--------------------+--------------+
| channel            | content_next |
+--------------------+--------------+
| 316757642527768577 | Content 6    |
| 384071823261696010 | Content 7    |
| 939746456632438804 | Content 2    |
+--------------------+--------------+

To also see the current and next msg_id, here's the adjusted SQL:

The adjusted fiddle

WITH cte AS (
        SELECT loop_msg.msg_id
             , channel
             , ROW_NUMBER() OVER w1 as rankx
             , COALESCE(
                           LEAD(loop_msg.msg_id)        OVER w1
                         , FIRST_VALUE(loop_msg.msg_id) OVER w1
                       ) AS msgid_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, content_next, msg_id, msgid_next
  FROM cte
 WHERE channel IS NOT NULL
;

The result:

+--------------------+--------------+--------+------------+
| channel            | content_next | msg_id | msgid_next |
+--------------------+--------------+--------+------------+
| 316757642527768577 | Content 6    |      4 |          6 |
| 384071823261696010 | Content 7    |      6 |          7 |
| 939746456632438804 | Content 2    |      8 |          2 |
+--------------------+--------------+--------+------------+

More detail:

You could try something like this. In this case, we can use ROW_NUMBER instead of RANK, since msg_id is unique and no two messages will have the same msg_id which would have the same RANK. Feel free to replace with RANK, if you wish.

The fiddle

Later, we can partition by channel to do this for each channel separately. Your question wasn't entirely clear about how you wanted to use channel.

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
;

The result:

+--------+---------+-------+------------+--------------+
| 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           |
+--------+---------+-------+------------+--------------+

The setup:

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')
;

Test cast #2, processing per channel:

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 (PARTITION BY chan ORDER BY msg_id ASC) as rankx
     , COALESCE(
                   LEAD(msg_id)        OVER (PARTITION BY chan ORDER BY msg_id)
                 , FIRST_VALUE(msg_id) OVER (PARTITION BY chan ORDER BY msg_id)
               ) AS msgid_next
     , COALESCE(
                   LEAD(content)        OVER (PARTITION BY chan ORDER BY msg_id)
                 , FIRST_VALUE(content) OVER (PARTITION BY chan ORDER BY msg_id)
               ) AS content_next
  FROM loop_msg
;

The result:

+--------+-------+---------+-------+------------+--------------+
| 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           |
+--------+-------+---------+-------+------------+--------------+

Finally:

We can also define a window clause to avoid rewriting the specification each time:

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)
;

Result:

+--------+-------+---------+-------+------------+--------------+
| 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           |
+--------+-------+---------+-------+------------+--------------+