Issues selecting rows with next row's IDs-CodePudding

This question is a follow-up to

When multiples channels are at the same place (same msg_id for different channel_id in loop_msg_status), the ordering doesn't work correctly and the values for next_content and next_msg_id are the current values.

Expectations

Back to the basics: I have 1 channel_id in loop_msg_status where I send content from loop_msg in roundrobin every 30 minutes if inbetween >= 10. I store the msg_id of the sent content in loop_msg_status for that channel, and 30 minutes later, I query again to see where I'm at and post the next content. For example take this row:

channel_id: 123456789012345672
msg_id: 4
inbetween: 30

When doing my SELECT in msg_loop_status, since inbetween >= 10, this channel_id should be selected with the next msg_id that comes after 4 and its corresponding content from msg_loop. Because there isn't a msg_id=5, the next one is actually 6. So the output would be like this:

channel_id: 123456789012345672
next_msg_id: 6
next_content: Content 6

Applying that same logic to the whole example, I'm expecting the following output (the colums such as the current msg_id and content are not needed but can be added for the sake of the example for readability). 5 of the 6 channels have inbetween >= 10, so for each of these channels I need their next_msg_id and next_content as such:

channel_id         | msg_id | next_msg_id | next_content
-----------------------------------------------
123456789012345671 | 2      | 3           | Content 3
123456789012345672 | 4      | 6           | Content 6
123456789012345673 | 6      | 7           | Content 7
123456789012345675 | 6      | 7           | Content 7
123456789012345676 | 8      | 2           | Content 2

CodePudding user response：

Updated to reflect your latest detail, where the next message id should be based on the unique / ordered list of message rows, without regard to the status table.

The outer join probably is no longer required:

WITH msgs AS (
        SELECT msg_id, content
             , 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
        WINDOW w1 AS (ORDER BY loop_msg.msg_id)
     )
   , cte AS (
        SELECT loop_msg.msg_id
             , channel_id
             , inbetween
             , msg_id_next
             , content_next
          FROM msgs AS loop_msg
          JOIN loop_msg_status
            ON loop_msg.msg_id = loop_msg_status.msg_id
     )
SELECT channel_id, content_next, msg_id, msg_id_next
  FROM cte
 WHERE inbetween >= 10
;

and finally:

WITH msgs AS (
        SELECT msg_id, content
             , 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
        WINDOW w1 AS (ORDER BY loop_msg.msg_id)
     )
SELECT channel_id, content_next, loop_msg.msg_id, msg_id_next
  FROM msgs AS loop_msg
  JOIN loop_msg_status
    ON loop_msg.msg_id = loop_msg_status.msg_id
 WHERE inbetween >= 10
;