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
CodePudding user response：
Final SQL with the schema (and expected result) given in the question:
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:
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.
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 |
-------- ------- --------- ------- ------------ --------------